Power BI: How to unpivot twice


Power BI: How to unpivot twice


Recently I had a customer providing data for a PowerBI report in the below format:

We have procedures containing several tests and for each test a result and target value.

As you might notice, the format of the data provided is not ideal for reporting purposes. Creating visuals based on data in this format is not easy (try it!). You would have to write a measure for each test and if an additional test is added to a procedure the setup, as is, is not workable.

The result we aim to obtain is this:

We want a column for ProcedureCode and ProcedureName (= no change in regard to the original setup). Next to these we want 1 column for test, 1 for the result value and 1 for the target value.

You can achieve this using a combination of pivoting and unpivoting columns and this blog post will show you how to do this.

Let’s go ahead.


Step 1: Unpivot columns

Select the columns that should remain unchanged (= ProcedureCode and ProcedureName).

In the Transform tab choose unpivot columns and unpivot other columns.

We use the ‘unpivot other columns’ approach to be future proof. If an additional test is added we have more columns that need to be unpivoted and by using the ‘other columns’ approach we don’t hardcode the columns to unpivot, we hardcode the columns that remain.

This results in a new format that better suits our needs. We now have an attribute-value pair for each of the original columns we unpivoted.


As you can see in the image above, we only have 1 column with values and in fact we want a column for results and a column for targets so we need to do some more transformations.


Step 2: Align attribute names

To achieve this we need to transform the value in the Attribute column to obtain 2 columns:

  • 1 containing the Type (Result or Target)
  • 1 containing the name of the Test (TestA, TestB, TestC,…)

NOTE: the name of the test should be identical for both the Result and the Target.

Depending on the column names you have in your dataset you need to pick the correct transformation to achieve this. This can be a split column by delimiter, conditional column based on the occurrence of a word, replace value, …

In my case the length of the prefix part (‘Result’ and ‘Target’) is identical so a split by number of characters was enough to achieve the 2 columns..

I also replaced the prefix ‘Test’ to have only A, B, C,… as a remainder to have a cleaner dataset.

Step 3: Pivot the Type and Value column

As a final step the only thing we still need to do is to pivot the Type and Value column to have the result we need.

Select the columns Type and Value. In the Transform tab choose pivot column.

By doing this we get the result we aimed for.

Our dataset is now ready to create some nice visuals.


For those interested in the M-code used in this demo:


Source =

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUQoIcgZRJkBsBsTmQGwKxRZKsTrRSkYQRUZQQTOwBEShGVyRMUSRMVTCAkmhBVgjSJEJRBGIMkJSgGRdLAA=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProcedureCode = _t, ProcedureName = _t, ResultTestA = _t, ResultTestB = _t, ResultTestC = _t, TargetTestA = _t, TargetTestB = _t, TargetTestC = _t]),

#”Changed Type” = Table.TransformColumnTypes(Source,{{“ProcedureCode”, Int64.Type}, {“ProcedureName”, type text}, {“ResultTestA”, Int64.Type}, {“ResultTestB”, Int64.Type}, {“ResultTestC”, Int64.Type}, {“TargetTestA”, Int64.Type}, {“TargetTestB”, Int64.Type}, {“TargetTestC”, Int64.Type}}),

#”Unpivoted Columns” = Table.UnpivotOtherColumns(#”Changed Type”, {“ProcedureCode”, “ProcedureName”}, “Attribute”, “Value”),

#”Split Column by Position” = Table.SplitColumn(#”Unpivoted Columns”, “Attribute”, Splitter.SplitTextByPositions({0, 6}, false), {“Type”, “Test”}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Split Column by Position”,{{“Type”, type text}, {“Test”, type text}}),

#”Replaced Value” = Table.ReplaceValue(#”Changed Type1″,”Test”,””,Replacer.ReplaceText,{“Test”}),

#”Pivoted Column” = Table.Pivot(#”Replaced Value”, List.Distinct(#”Replaced Value”[Type]), “Type”, “Value”, List.Sum)


#”Pivoted Column”







Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...