Non-aggregate pivot with multiple rows in Power Query
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.
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.
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:
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…