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
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.
Excel & Power BI Training
Step 4: Close & Apply
Click Close & Apply from the Home tab and this will apply all the pending changes on your transposed data.
Learn Power Query Tips & Tricks – Watch Video
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.
You can acquire knowledge more :
- Replace Values
- Split First & Last Name
- Learn How to do Reverse Rows By Power Query
- Read How to Group Rows And Get Counts By Power Query