Power BI Blog: Combining Double Headers

Welcome back to
this week’s edition of the Power BI blog series.  This week, we look at how to deal with pivoted
data that has two rows of headings.

When dealing with pivoted data, it is
common to come across double headers, i.e., where two rows of text make
up the table heading(s).  For example,
the following table contains sales data from different countries on different
products, and a sub-category Product Type:

Each column is a unique Product Type of some product, e.g., the second column represents ‘type B’ of
the product ‘Carretera’.  We might
want to drill down to the most granular level, to have a simple two-dimensional
table with row and column headers.  However,
we can’t simply remove Product, since it wouldn’t make sense to have ‘type
A’, ‘type B’ and ‘type C’ as column headers.

That means we need to merge Product and Product Type.  How do we do
that?  When size of the data is small, we
can simply transpose the table and merge. 
Continuing from the above table, we can go into Power Query Editor and
first transpose the table.  Then to merge Product and Product Type (columns now), we hold down CTRL key to select them, and in Transform -> Text Column we click Merge
Columns to pop up the following dialogue box:

We can specify a separator and a new column
name to merge the two [2] columns.  Transposing
back, we have the following table:

for which we can promote the headers and
continue from there.

But what if the data is humongous?  It is not uncommon to have thousands or
millions of rows of data in real datasets, and it is hardly a good idea to
transpose such datasets as done for the above.  There is a better way to do it, which we will demonstrate using a dataset of child account
spendings with 120,000 rows of fake data.  

Extracting
Headers

This is a snippet of the dataset:

The first two [2] columns contain account
numbers of the child accounts and their linked parent accounts.  The third column describes types of the child
accounts.  Other columns contain
quarterly spendings from 2018 to 2022.  We
generated 120,000 rows of random numbers and size of the dataset is close to 31
megabytes.

The same problem of having double headers
occurs here.  Both of the first two [2]
rows are necessary, and we would like to obtain a merged row of headers in the
form ‘Year – Quarter’.  The gist of our
technique is to extract only the first two [2] rows, transpose and merge them,
and then insert the merged headers back into the table.  We have developed a module of Power Query M :

Let’s go through the code.

We use the Power Query M function Table.FirstN to extract the two [2] rows of headers from Source, the original table:

and in the next line we specify all columns
to be type text so we can merge later:

The function Table.TransformColumnTypes is the corresponding function for changing data type for columns, and its
second argument expects a list of lists in the format:

Here, we use List.Transform and Table.ColumnNames functions to transform all columns at once, without listing names of all
columns.  The function Table.ColumnNames returns names of the columns in the table HeaderRows as a list of text,
and the function List.Transform transforms the list to a list of lists,
to use as the second argument in Table.TransformColumnTypes.

The next two [2] functions, Table.Transpose and Table.CombineColumns, come from transposing and merging actions.  Let’s have a look.  In Transform -> Table, we can click Transpose and the two [2] header rows become:

Then, we hold down the CTRL key to
select the two [2] columns, and in Transform -> Text Column, we click Merge Columns and specify a separator and a name for the output column:

At this point we have obtained the merged
headers that we wanted.

To insert the merged headers back into the
data table, we use three [3] functions: Record.FromList, Table.InsertRows and Table.Skip.  The first line of M code:

first uses square brackets ([]) to
extract column Header from table HeaderMerged as a list, and then
the function Record.FromList converts the list into a record, with
column names of the original table Source as field names.  The second line of M code:

The inner-most function Table.Skip excludes the first two [2] (unmerged) header rows of the original table.  Specifying a zero [0] at the second argument,
the function Table.InsertRows inserts at the beginning of the “unheaded”
table a list containing the record Headers.

This is a snippet of the output table, with
merged headers:

We can promote the first row as headers and
complete with minor modifications.

That’s it for this week.  In the meantime, please remember we offer training in Power BI which you can find out more about here.  If you wish to catch up on past articles, you can find all of our past Power BI blogs here.