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.

Download the Excel file

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).