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!
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
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
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 🙂