Excel Hash is back! 4 ingredients, 5 contestants and we all had to create a solution that integrates all 4 ingredients.

The Contestants:
Mr. Excel, Bill Jelen
Mynda Treacy
Jon Acampora
Leila Gharani
Oz du Soleil

The Ingredients:
XOR, a Linked Picture, a Dynamic Array function, an Icon

Here is the link to the playlist to see all videos

URL for voting:
https://forms.office.com/Pages/ResponsePage.aspx?id=QXR5-MbycU6IQ9aFc_tUvFg-bImtIN1EqJE5kvHXqKxURUw4RlIxUFFRT1FSWTkxR0g0MzBHVUdNQi4u
#ExcelHash
#XOR
#ExcelonFire

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Duplicate Query in Power Query is underused and under-discussed. This video shows one way to use Duplicate Query to save from doing a lot of work over and over again.

This video also shows split column; split column non-digit to digit; a little M-code. There’s great stuff to keep you from having to work so hard to get cooperation from your data.

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

This video is a 2-for-1. You get the new XLOOKUP and a Full Anti Join in Power Query (Get & Transform).

What is a full anti-join? If there was a class held on Monday and Wednesday and you’d like to know who attended one session or the other BUT NOT BOTH, you would need a full anti-join. One way to do this would be to do a left anti-join and then a right anti-join, then append the results.

Another way, as I show in this video, is to append the 2 datasets, create an inner join, then use an anti-join to isolate the people who are in one group or the other but not both.

XLOOKUP!
WOW! This was revised within the last 24 hours to include a 6th argument for: “if not found.”

This is exciting because a lot of times we’ll do a lookup and anticipate results where the lookup value isn’t found. The fix is to wrap a lookup formula in the IFNA function. No more!

The 6th argument, “if not found” is a valuable feature because we no longer need the extra step of wrapping IFNA around the formula. And the 6th argument is optional.

#FullAntiJoin
#PowerQuery
#InnerJoin
For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

After grouping data in Power Query, in this example, the data is exactly how we want it. It’s grouped into the right rows in the column.

BUT! It’s in a collapsed column of tables. If we expand, the data will fly apart. So, how do we get a column of data out of a table and keep the data in the appropriate rows and cells?

We have to use the Power Query function: Text.Column

In this video you’ll see
– Camera Tool
– using a line-feed as a delimiter
– how Table.Column is used
– Grouping in Power Query
– Extract values from a list

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2