This is a tutorial about Power BI unpivot columns. I’m going to show you how to unpivot multiple column combinations. You may watch the full video of this tutorial at the bottom of this blog.
This is part of an unpivot variation series, which is all about turning your data into a proper tabular format that is suitable for analysis. Most transformations can be performed using the user interface.
The example that I’m going to show you is coming from a question raised by an Enterprise DNA member in the forum. If we look at the source table, we see one row. The desired layout shows three records where that single row from the source table is split up by overtime type.
So, we need to create an attribute that says something about the values in that record, and then combine the hours and the costs for that overtime type.
There are several ways to achieve this, but I’m going to share with you a solution that uses a powerful M function, that’s not available through the ribbon called List.Zip. So let’s examine how that works.
Power BI Unpivot Column Using List.Zip
In the top left, I zoomed into the data pairs and we can distinguish three overtime types. The function List.Zip takes a single list that contains a nested list and then returns a single list with a nested list as a result.
Our first list will contain the attributes that send something about the values on that record. The second list will contain the hours and the final one is the cost.
If we look to the right, we can see that List.Zip then combines those values from the nested list based on their position. So, everything on the first row on the left gets combined into a new list on the right. And the same thing happens for every other row in the source list that we provided List.Zip.
Now, let’s go over to Power Query. I’ve already mentioned that List.Zip is not available through the ribbon, so we have to write some actual M to use it. And we can do that if we add a custom column.
So, via the mini table in the left corner, I’m going to select Add Custom Column.
Let’s enter List.Zip, then put an opening parenthesis. It seeks a single list with lists, right? So I’m going to use the list initializer to create that list.
Then, we see text values, one for each overtime type. I’m going to add a comma and create a new list. Remember that List.Zip combines the values based on their position in the list. So, we must pass the values in the exact same order. Then, I’ll put a curly bracket for that list.
Our final list is going to contain the cost, so I’ll put a comma and opening curly bracket, and select the values in the same order. And again, add a closing curly bracket for that list, then a closing curly bracket for our lists containing lists, and another closing parenthesis for the function. Then, click OK.
Now, we no longer need the value columns here, right? So I’m going to select the first one, press shift, select the last one, then click and Remove Columns.
I’m going to click on decide in the white space here to see the contents of our lists. We can see that we have a list containing multiple lists, and that’s exactly the output that List.Zip generates.
Expand To New Rows & Extract Values
So with those sideways arrows, I’m going to expand to new rows.
And it generated three records. When I click on to the side in white space again, we can see that we have the values for that first record.
We can extract those by clicking those sideward arrows again and click on Extract Values, then select a delimiter. I’m going to go with a tab and press OK.
All the values are not combined in a single column, so on the Transform tab, we can select Split Column, then click on By limiter.
Then here, we select the tab and press OK.
It didn’t detect the data types correctly, so by pressing down on shift, I’m going to select the last column and on the Transform tab, select Detect Data Types.
And now, let’s give these columns proper names.
***** Related Links *****
How To Unpivot Columns In Power BI
Unpivot And Pivot Basics In Power BI – Query Editor Review
Power BI Query Parameters: Optimizing Tables
Conclusion
In this tutorial, I’ve shown you how to unpivot multiple column combinations without using any of the Power BI Unpivot Columns commands that are accessible on the ribbon. Here I used List.Zip to unpivot sets of data in Power Query.
I recommend that you explore more of this function and implement it in your own Power BI reports.
All the best!
Melissa
[youtube https://www.youtube.com/watch?v=U1O5LfMZP0s?rel=0&w=784&h=441]
