Have you ever wondered how you can convert a row to a column in Power Query? This article teaches you the easiest approach to achieving this.
Let’s get started.
Introduction
To get started, insert your data into Power BI. Select the worksheet you want to work with, and click on Transform Data.
For this tutorial, I will make use of Excel data with Alternate Rows (as displayed below). You can download the data here.
Here, click Transform Data and it will take you to Power Query Editor.
Now, this is Power Query Editor, and this is where we will convert the rows into columns.
Create a Conditional Column
Now, to convert the row into a column, we are going to create a conditional column. So, go to the Add Column tab and select Conditional Column, just right under it.
That presents us with the Conditional Column window.
You can rename the column if you want. Now, it is time to create a condition (I renamed mine as New Address).
The condition I created is that “If the column ID equals null, then the output should be the Location (where the address is). Else, return a null.”
This is what really makes the difference to just duplicating the column because by bringing the null values, we will be able to fill them in later.
Now, click OK.
By clicking OK, you will get this additional column added to you your data. But it does look impressive yet, does it?
We still have the address, but not in the same role as the ID and values.
Now, the next thing to do is to select the New Address column and go to the Transform tab click on Fill and select Up.
This will fill the null values with the addresses. And now, we will go on to remove the null rows, because they are no longer needed.
To remove the null rows, filter the ID column, uncheck null, and click OK.
Now we have our data. We have just converted from a row into an additional column. You can now close and apply (in the home tab), and continue with your report.
Conclusion: How to Convert Row to Column in Power Query
Now, that is how to convert rows to columns in Power Query. And do not forget to practice this with as much data as possible.
Thanks for reading.