Non-aggregate pivot with multiple rows in Power Query
The problem
Let’s say I have a vehicle hire company and three vehicles, a Van, a Truck and a Car. I want the names of those who rented the vehicles in an easy-to-read table.
Currently the data is in a flat file format.
Scrolling through this data is going to be tedious, I need a table like below.
As you can see, the flattened table has been pivoted and extra rows have been generated for where there are additional drivers. The “generating extra rows” part makes the process a little more complex but this is nothing that Power Query can’t handle.
But firstly,
How not to do it
Some people might say, “We’ll just pivot the table on the vehicle column and use the Don’t Aggregate as the Aggregate Value Function”.
But, as you can see, errors occur where there are additional drivers.
We do need to pivot, but we also need to write some code to generate those extra rows.
The solution
This is a generic function which you can use:
(Source as table ,ColToPivot as text ,ColForValues as text)=> let PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))) ,#"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _) ,TableFromRecordOfLists = (rec as record, fieldnames as list) => let PartialRecord = Record.SelectFields(rec,fieldnames) ,RecordToList = Record.ToList(PartialRecord) ,Table = Table.FromColumns(RecordToList,fieldnames) in Table ,#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)) ,#"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames) ,#"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames) in #"Expanded Values"
It’s better to see the code in the Power Query editor however. That way, you can click on the individual steps and see how the query transforms the data. For this reason, I’ve uploaded an Excel file that contains some data along with the query shown above. There are also some notes about the query below.
For you Power Query first-timers, I’ve made a quick guide on how to open the Power Query editor from Excel and how to explore those queries.
How to open up Power Query from Excel
One of the great things about Power Query is that the queries are run in a step by step process and it is very easy to check each step in the “Query Settings” pane on the right-hand side of the Power Query editor.
Some background to the code
List.Buffer (native function)
When you have a query calling a particular list multiple times it is best to buffer the list in memory by using the List.Buffer() function so it is not computed each and every time.
TableFromRecordOfLists (custom function)
The other aspect I think need to explain is about how I chose the native functions that comprise the custom “TableFromRecordOfLists” function. It is worth reading through the MSDN Power Query M function reference, picking out the more interesting functions and playing with them. This definitely paid off; I knew I needed something akin to a jagged array in a table and that Table.FromColumns would give that to me. The other native functions were more “standard” and were researched on the fly.
Using conditional formatting in excel.
The last aspect is non-Power Query related but very important in making the table readable. The black horizontal lines spanning the table that separate the table into their respective days are created in Excel by applying conditional formatting using a formula. First, select the data in the table (or quickly, select one the values in the table and press Ctrl-A). Then click on Conditional Formatting in the home tab, click on “New Rule” and then click on “Use a formula to determine which cells to format”. If K5 is the topmost cell in the data of the date column, then apply the formula:
=$K5<>Offset($K5,1,0)
and use a bottom border as your formatting. (The reason we do not write $K5<>$K6 instead is because of a bug in Excel. If the table expands upon a refresh then the conditional formatting will not expand correctly).
Hi – thanks for this solution. I cannot get the Table name to read, and unfortunately, my company blocks the file download. What do you put in the Table field of the pop up when you invoke the function? My data table is “Table4” but it gives me an error…
Hi Beth, if you want to import the table into Power Query, click on the table in Excel (so at least one cell is selected within the table) and then click on “From Table/Range” on the Data tab. This will then open up Power Query and your table will be there. If you still have issues, reply back with the error message.
I’m still running into issues. I had a table in excel, I select From table/range, and get the data in the query editor window. What I can’t figure out is where to place the generic function, to call it for my table (which by the way it Table1). I tried adding it in a blank query, but then I get the error.
= Query1(“Table1”, “PackageEffDate”, “HPCC”)
Expression.Error: We cannot convert the value “Table1” to type Table.
Details:
Value=Table1
Type=Type
I’m very new to Power Query and haven’t figured out the structure of the language, so I don’t know how to interpret/read/update code in the advanced editor.
Thanks for your help. Beth
Hi Beth, you need to get rid of the quotation marks surrounding Table1 in the function to reference the table.
Cheers, Cameron
Just wanted to say thanks for this solution. It helped take a two column table and pivot it gracefully using your custom function.
Instead of
X Y
A 2
A 3
B 4
I can have
A B
2 4
3
Hi Cameron,
thanks a lot for your solution. It worked perfectly until now. Are you aware of recent changes to these functions? Some of my data appear in wrong order after pivoting.
Best regards
M
Hi Marius, I’m not sure, Power Query often assumes that order is irrelevant.
I actually created another version of this function a while back
https://github.com/camwally/Power-Query/blob/master/fNonAggPivotMultRows2.pq
If this doesn’t work try take the idea of buffering the table first (as you can see in the second function) and put it in the original function.
Let me know how this goes.
It works really fine.
This solution is truly brilliant!
Thanks a lot!
Hi Cameron,
Thank you very much for uploading this. I have a question about cross joining the list, how should I adapt the code to make this possible. For example, I would like to have the following:
List A
Car
Van
Bus
List B
Mercedes
Peugeot
Renault
Result:
Car Mercedes
Car Peugeot
Car Renault
Van Mercedes
Van Peugeot
Van Renault
Bus Mercedes
Bus Peugeot
Bus Renault
The example is a simple example, but it should be applied on +- 20 lists. This is important for filtering my results. Hopefully you can help me.
With kind regards,
Peter
Hi Peter,
I’m not sure if you have lists or tables of values but I think the code below will explain things
let
listA = Value.ReplaceType({“Car”,”Van”,”Bus”}, type {text}),
listB = {“Mercedes”,”Peugeot”,”Renault”},
tableA = Table.FromColumns({listA},{“A”}),
tableAWithB = Table.AddColumn(tableA, “B”, each listB, type {text}),
tableAWithBExpanded = Table.ExpandListColumn(tableAWithB, “B”)
in
tableAWithBExpanded