Pivoting Text Data (advanced)

Overview

Suppose you have text data given to you like shown below. However, unlike in other situations you’ve encountered, the data here does not follow any kind of repeating pattern, like every 3 lines being a new record. The entries are completely random.

You would like to pivot the data, such that on each date, you can more clearly see the persons that hired a van, the persons that hired a truck, and the persons that hired a car. A final result that looks something like this:

This article explains how to do this by building a function.

Steps

1. Load the data to power query

It should look as it does in Excel, something like this:

2. Set-up Table.Pivot function parameters

• Enter the following code in the advanced editor:

let
    Source = FlatTable,
    ColumnForValues = "Person",
    ColumnToPivot = "Vehicle",
    PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColumnToPivot)))
in
    PivotColNames

The above code sets up the parameters needed for the Table.Pivot function, that will be used by the function to properly transform the data:

• ColumnForValues - is a scalar value that refers to the name of the column, that currently holds the data values that will be shown, after the data has been pivoted
• ColumnToPivot - is a scalar value that refers to the name of the column, that currently holds names of the columns that will be created, after the data has been pivoted
• PivotColNames - is a list that holds the distinct values in the column referred to by ColumnToPivot

3. Call the Table.Pivot function

• Next, call the Table.Pivot function, by adding some extra lines, so that the code looks like this:

let
    Source = FlatTable,
    ColumnForValues = "Person",
    ColumnToPivot = "Vehicle",
    PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColumnToPivot))), 
    #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColumnToPivot, ColumnForValues, (_) => _)
in
    #"Pivoted Column" 

The last (fifth) argument of the function tells it to load the data values as lists. As you can see below, clicking on any individual cell will reveal the list of data that underlies it. As the data is text type in nature, if there are multiple values in a particular cell, they cannot simply be summed up and presented as a single number (like numeric data can). This is the main challenge with pivoting text data like this.

4. Create a sub-function

• Next, create a sub-function that takes each record (called rec below), and all headings of the record (called fieldlist), and converts it into a table. This table conversion is needed so that the data properly expands (shown in a later step).

The code, with the sub-function in it, should now look as follows:

let
    Source = FlatTable,
    ColumnForValues = "Person",
    ColumnToPivot = "Vehicle",
    PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColumnToPivot))), 
    #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColumnToPivot, ColumnForValues, (_) => _),
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table
in
TableFromRecordOfLists 

• Now, call the sub-function, by adding a custom column, with the following expression:

The table, with the added column of table objects, should now look like this:

Notice how the table combines the 3 preceding lists together into one object. This is exactly what is needed.

5. Remove columns, and expand fields

• Next, keep only the ‘Values’ and ‘Date’ columns, by selecting them, and then right-clicking, and choosing the option ‘Remove Other Columns’
• The data should look as follows:

• Finally, expand all fields from the column called “Values”

The data should finally show as follows:

• Click Close & Load to return the properly pivoted data results to Excel

6. The ‘M’ code

The final ‘M’ code to the query (which itself could be turned into another more generalisable function that could work on any dataset), is shown below:

let
    Source = FlatTable,
    ColumnForValues = "Person",
    ColumnToPivot = "Vehicle",
    PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColumnToPivot))), 
    #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColumnToPivot, ColumnForValues, (_) => _),
    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"