Power BI Intermediate Level: 32 - Processing Tables with Two-Row Headers

Table of Contents?|?Power BI Report File ?| Sample Input

Short summary: Sometime you get data where the header effectively spans two rows, making it hard to process. I will show you how to dynamically make the two-row header into a regular one-row header, preserving the information rather than discarding the first row.

Usecase

It has happened to me and my colleagues many times that we get table data where the header spans two rows, such as below.

Like in the previous article about Unpivot, this is a case of easy to read but difficult to process. This is because a table needs one header row with unique header. If we were just to remove row 1, we would get non-unique column headers, and therefore Power BI would add number suffixes on the otherwise duplicate column names, such as name them Name1, Name 2, etc. and we would obviously lose the contractor information contained in row 1. What I instead do in such cases is to add the information of row 1 as a prefix for the column headers in row 2, such as below, therefore keeping the information.

Solution

To do this in Power BI, the straight-forward way would be to use a functionality Merge Rows, which does not exist. There is however a Merge Columns functionality, so let us use this together with Transpose.

After loading the data into Power BI, transpose the table using Transpose under the Transform ribbon:

Note that if you have a merged cell in Excel, such as for Contractor A, the information is saved only in the left-most cell, the remaining cells of the merged cells are empty (as marked by null). Let us fill up those cells by selecting Column1, and then on the Transform ribbon select Fill Down.

Now select the first two columns (corresponding to the first two rows of the original input data) and under the Transform ribbon, select Merge Columns.

In the window which opens, select a period (“.”) as a custom separator.

Finally, transpose the table back and use first row as headers in the Home ribbon. Notice how you get column headers in the desired format, preserving all information in a systematic manner: General.Project, Contractor A.Name, Contractor A.Email, Contractor B.Name, Contractor B.Email, etc.

And there you have it, retraining all the information with nice, structured column headers.

Extended Solution

If you want to further process the data, you notice that at the moment, you cannot filter by contractor for example. To make this work, we use Unpivot again, like in the previous article. Let first create a new query by referencing the one we created above, just so that we have both results available.

Select the column General.Project and then under the Transform ribbon select Unpivot Other Columns.

So far this is like we have done in the article on Unpivot. Now let's add the extra step of splitting the attribute column into the two columns Contractor and Name. Do this by selecting the Attribute column and then Split Column, By Delimiter.

Select custom delimiter and use dot ("."). Further select Each occurrence of the delimiter and number of (maximum) columns to split into as 2. These options come in really handy for more difficult use cases.

Finally rename the columns and you get a table which you can easily analyze and plot, such as counting the number of contractors or number of provided email addresses over projects.

In the next article, I will show you a technique to make your report much more interactive by implementing a button which can swap out visuals. This technique can be extended to a wide variety of use cases.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?Buttons for Toggling Visuals, Filter Panes, and other Objects