Power Query: M(erry) Christmas

Welcome to our Power Query blog.  This week, I take a break from all things fuzzy to help out a familiar face.

I have received a request from a Mr. S Claus, who is having some issues with his workforce.  The Elf Service are complaining about being continually shelved, and Mr. Claus has resorted to asking the reindeer to create a Present List.  They are refusing to pull together, so he has the following Table:

He would like one list, and to avoid upsetting the Elves again, he needs any duplicates to be removed.  Since he’s quite busy, he would like me to automate this process.

I start by naming the Table SC_List, by changing ‘Table Name:’ in the ‘Table Design’ tab.  I right-click on the Table and choose to ‘Get Data from Table/Range’.  I now have a query SC_List:

I may remove the ‘Changed Type’ step, as all my data is text, and I will not be keeping these columns.  I note that if I right-click on a column header, I have the option to ‘Add as New Query’:

If I did this to each column, I could create nine [9] queries, which may then be appended together.

Let’s look at the M code generated when I create a query for Dasher:

The M code is simply:

This gives me an idea.  Rather than creating lots of queries and appending them, I may add lists together using the function List.Combine() (which I last looked at a long time ago in ‘All Over Running Totals’).  For example, to combine the Dasher and Rudolf columns I could use the M code:

This is looking good; let’s add the rest of the herd.

The M code is:

Perfect, 90 rows are now on the list!  Now, I need to get rid of the duplicates.  I can do this without adding another step.  Instead of List.Combine(), I use List.Union():

The M code is now

For this example, List.Union() is ideal as it extracts items from all the lists but only shows them once.  I rename the query SC_Elves_List, and ‘Close & Load’ from the Home tab:

The list is even in the Elves favourite colour!  When Rudolf decides to add more ‘toys’:

I simply refresh the query and the new rows appear:

Mr. Claus is happy (actually, quite jolly 😉).  Merry Christmas! 

Come back next time for more ways to use Power Query!