Part 2: Combination of rows of tables list in Power Query

Part 1 describes approach showing how to work with List.Generate, Table.FromRecords, List CrossJoin in Get & Transform (aka Power Query, M language).

Homework is done. Advice is digested.

And I’m ready to introduce another solution to combine multiple tables rows.

It is so simple, and can be done in UI!

All regards and honor should go to Imke Feldman (http://www.thebiccountant.com/) as she found that “epic Pokemon”.

I looked at this task as a programmer, thinking about loops and iterations.

Imke – completely differently – as simple user, don’t even thinking about programming, as she said :-).

Remind, we start from four tables

And want to mix all rows to get all possible combinations (indexed).

How to do this using UI only?

Take 1st table and add all other tables in custom columns

Then expand each

At this point you may have same feeling as me – “too many columns in a table”, and have tension to collapse them somehow.

Add Index column at this step to numerate rows.

And then use Table.UnpivotOtherColumns for just added Index column

Result looks better

But we want Command, Field and Value as columns in resulting table.

What prevents us to use Table.Pivot right now is numbers generated after Expand

This can be solved by Table.SplitColumn – again, using UI we ca do this

And now we have we use Table.Pivot on column with headers of initial tables, select target column and Pivot it

Left to remove unnecessary column

and voilà

Bravo Imke!

Below is same solution represented as function, which also can be found in my library here.

Power Query intellisense for NotePad++: http://power-bi-usergroup.blogspot.ru/2015/11/creating-editor-for-power-query-with.html

Lazy option: http://www.mattmasson.com/2014/11/notepad-language-file-for-the-power-query-formula-language-m/

Afterwards

I had a strong feeling that my initial solution is not optimal. And I hoped on support of the community. I knew someone will introduce improvement and happy to share two ideas – from Bill Szyzs and Imke Feldman.

Link to file with all three solutions.

I like how Power Query / Excel / Power BI community works and proud of being part of it.

Productivity of solutions is a separate question. What is faster: “unpivot / pivot” or “loops through lists” – I don’t know. Tests with big tables must help to measure timing…

I personally like approach without programming. Even if it is not obvious, as usage of someone’s function, it remains feeling that everything is under control, no “wise magic” inside, user can follow “applied steps”.

Great that Yoda has Excel colors 🙂