Transposing a data set is ultimately rotating data from rows to columns, or from columns to rows. To understand this concept, an example with the picture is given below.
Transposing Data By Power Query
Let’s go through the steps in detail for transposing data.
- Step 1: Open the data in Power Query
- Step 2: Transpose the data table Select Transform > Transpose
- Step 3: Use the first rows as headers
- Step 4: Close & Apply
Step 1: Open the data in Power Query
Open the data set in Power Query that you want to transpose. The picture below shows the data that we want to transpose. We will transpose this data to rotate it from rows to columns.
Step 2: Transpose the data table
Select Transform > Transpose
It flips the data from rows to columns. Power BI Transpose option works on the entire Data table as shown in the below picture.
Note: When you transpose the data table, it eliminates the existing column headers and replaces it with the default Power Query column headers like Column1, Column2….and so on.
Step 3: Use first rows as headers
Go to Transform > Use First Row as Headers button > Use First Row as Headers option
This will show all the details of individuals column-wise.
Step 4: Close & Apply
Click Close & Apply from the Home tab and this will apply all the pending changes on your transposed data.
If you wish to learn Power Query and want to become an expert in Data Cleaning and Data Transformation Techniques, then check our Advanced Power Query Course which comes with instructor-led self-paced training and lifetime access and updates.