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 🙂

Advertisements

One thought on “Part 2: Combination of rows of tables list in Power Query

  1. Simon 2017-05-15 / 17:36

    Hi, I-ve spent 3 hours reading your post, but I still can’t replicate Imke’s solution. In particular, when I try to add a custom column, the only content in the “available columns” is from the tables itself. Also, apart from that, I don’t see any way to add “table” within the column. I’ve googled on both these, and haven’t found any references let along any solution. Any insight anyone could provide is much appreciated.
    regards
    Simon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s