It is very common to need to transform data from one “shape” to another “shape” before it can be used inside Power BI for analysis (although many beginners don’t realise this). One such example is shown below, where the data in the table on the left hand side needs to be transformed into the table on the right hand side. As you can see on the left, column A contains the attribute and column B contains the value of the attribute. Every 4 lines of data is 1 record. This specific problem is very common problem when your only source of data is from an extract (eg csv) from some other system, particularly older systems where you can’t change the format of the data extract.
The trick to solving this problem is to pivot the text data into a tabular shape. I learnt how to solve this problem from my friend Imke Feldmann at The BIcountant.
You Cannot Pivot Text Data Directly
This is how NOT to do it. If you click on column A above and select “Pivot Column” from Power Query, you will get the results shown below.
This approach will not solve this problem because Power Query doesn’t know how to uniquely identify each record set, and that is a hint on how to solve this problem.
How to Pivot Text Data
The trick to solve this problem is to first stamp each set of 4 rows as a unique record.
In the example above, each record is spread over 4 consecutive rows in the table, so we first need to stamp a unique record number ID to each set of 4 rows that makes up a single record. Once we have this unique identifier for each row, we can use the identifier to pivot the data to produce the desired results.
Steps for Pivoting Text Data
I completed the demo below using Power Query for Excel, but of course it also applies to Power Query for Power BI. You can download the sample workbook here.
I loaded the table into Power Query, selecting Data tab (see 1 below) and then clicking on From Table/Range (see 2 below).
In the Power Query window, I went to the Add Column tab (see 1 below) and clicked on Index Column (see 2 below). A new numeric column is added (see 3 below).
Next, I converted this numeric column so that it identifies (stamps) each record uniquely. For this I went to Transform tab (see 1 below), Standard (see 2 below) and clicked Integer-Divide (see 3 below).
I divided by 4 (see 1 below) as that is the number of rows in each record.
There is now a unique integer in the new column that identifies each record (see 0, 1, 2, 3, 4, 5 below) in the table.
Step 4: This is the final step.
I selected the first column (see 1 below), went to Transform tab (see 2 below) and then selected Pivot Column (see 3 below).
In the Pivot Column dialog box (see 1 below) I selected the Values Column to be Column2 (see 2 below) and then selected Advanced Options (see 3 below) and set to Don’t Aggregate (see 4
This pivoted the data as shown below.
I do not need the index column anymore and I deleted it. I then fixed the data formats, and that’s it.
Once I load this table back into Excel beside the original table, it looks as in the first image that we started with in this article.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here http://xbi.com.au/pqt
You can learn similar tricks and tips to this on Power Query with illustrative examples. All the examples demoed in the course are also available to download in the course material so that you can practice along as you view the videos for an in-depth understanding of the concepts.