Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Harisai9315
Helper I
Helper I

What is the alternative to transpose when data that suppose to be in row format is in column header?

I have dataset where information that should be in row format is in column format. Example: Product Name. Instead of product name being in 'by row' format vertically, it is as column headers. This makes it very difficult to analyse data by product. I have tried transpose but it spoils the entire dataset. And the dataset is so big that changing it manually will be very time consuming. Appreciate suggestions and workarounds. Another example: Workshop Assessment Characteristics that should be by row with their scores in the next column by row, instead, each characteristic is a column header with the scores in the row below. This makes it so difficult to see the data on visuals eg: workshop characteristics by average score. Please assist. Thank you. 

1 ACCEPTED SOLUTION
Harisai9315
Helper I
Helper I

UNPIVOT COLUMN FEATURE IN POWER BI

Dear members of the MS Power BI Community, after the suggestion given by HotChilli follwed by loads of research from my side, the alternative solution to transpose data is "Unpivot Column". However, it depends on the size and complexity of your dataset. For large and complex dataset, the following steps may help before you reach the final unpivotting step. 

What is Unpivot Column: It is simply transforming the data that "suppose to be recorded in row format vertically", but has been recorded in "column format". Simple example: Imagine you have the names of people and their exam results - where their name is in column header format follow by results in the next row. 

STEP 1: Open your Power Query Editor

STEP 2: Click on the dataset in which you need to unpivot your columns
Now is the time you are going to divide your dataset into several different parts. (For eg: necessary and unnecessary information). The idea is if you have 5 different types of information, you will disect the datatable into 5 different datatables, later you can link them under relationship using the one common variable that is present in all the datatables example "Name". 

STEP 3: Right Click on the dataset that you have selected and click "Reference". This will dupicate your dataset. This step can be repeated every time you want to preapre a new table out of the original table just to retain one of the 5 types of information you want. Assuming you have 5 different types of information, then you will have 5 new datatables (just dont forget to connect them under relationships later). 

STEP 4: Keeping the original, now delete the unnecessary columns from your "Reference" dataset. Keep columns like (name, the scoring factors etc). 

STEP 5: Highlight only the columns you want to unpivot (eg: 20 scoring factors which are suppose to be in row format but are in cloumn header format). Note: Keep other colums as they are. 

STEP 6: Right click on the selected highlighted columns and click Unpivot Columns. 

STEP 7: Now you will realise all the column headers will be in row format. So, example last time you had John with his scores in one row across 20 different columns, now you will have the name "John" repeated 20 times in row format (20 different rows), for the 20 factors as it has been unpivoted into row format. That is completely okay. The data in Power BI must be in that format for effective visualization. 

STEP 8: Now, rename the original datatable and your referenced datatable, press control and select both or more referenced datatables, right click and form a new group with a new title, so you know that all the datatables are part of one specific datatable. 

STEP 9: Close and Apply. Go to relationships and connect the datatables using the one common column that is present in all the datatables (eg: Name). 

Hope this solution helps for those dealing with complex datasets that need complex unpivotting. If you have questions, or further suggestions, please mention it below. Let's learn together. "Mark this as solution if it helped you"! 


View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

Future readers may also want to check out Microsoft's documentation on unpivoting:

https://learn.microsoft.com/en-us/power-query/unpivot-column

Thank you for sharing @AlexisOlson 

 

Harisai9315
Helper I
Helper I

UNPIVOT COLUMN FEATURE IN POWER BI

Dear members of the MS Power BI Community, after the suggestion given by HotChilli follwed by loads of research from my side, the alternative solution to transpose data is "Unpivot Column". However, it depends on the size and complexity of your dataset. For large and complex dataset, the following steps may help before you reach the final unpivotting step. 

What is Unpivot Column: It is simply transforming the data that "suppose to be recorded in row format vertically", but has been recorded in "column format". Simple example: Imagine you have the names of people and their exam results - where their name is in column header format follow by results in the next row. 

STEP 1: Open your Power Query Editor

STEP 2: Click on the dataset in which you need to unpivot your columns
Now is the time you are going to divide your dataset into several different parts. (For eg: necessary and unnecessary information). The idea is if you have 5 different types of information, you will disect the datatable into 5 different datatables, later you can link them under relationship using the one common variable that is present in all the datatables example "Name". 

STEP 3: Right Click on the dataset that you have selected and click "Reference". This will dupicate your dataset. This step can be repeated every time you want to preapre a new table out of the original table just to retain one of the 5 types of information you want. Assuming you have 5 different types of information, then you will have 5 new datatables (just dont forget to connect them under relationships later). 

STEP 4: Keeping the original, now delete the unnecessary columns from your "Reference" dataset. Keep columns like (name, the scoring factors etc). 

STEP 5: Highlight only the columns you want to unpivot (eg: 20 scoring factors which are suppose to be in row format but are in cloumn header format). Note: Keep other colums as they are. 

STEP 6: Right click on the selected highlighted columns and click Unpivot Columns. 

STEP 7: Now you will realise all the column headers will be in row format. So, example last time you had John with his scores in one row across 20 different columns, now you will have the name "John" repeated 20 times in row format (20 different rows), for the 20 factors as it has been unpivoted into row format. That is completely okay. The data in Power BI must be in that format for effective visualization. 

STEP 8: Now, rename the original datatable and your referenced datatable, press control and select both or more referenced datatables, right click and form a new group with a new title, so you know that all the datatables are part of one specific datatable. 

STEP 9: Close and Apply. Go to relationships and connect the datatables using the one common column that is present in all the datatables (eg: Name). 

Hope this solution helps for those dealing with complex datasets that need complex unpivotting. If you have questions, or further suggestions, please mention it below. Let's learn together. "Mark this as solution if it helped you"! 


Harisai9315
Helper I
Helper I

Hi HotChilli. Thank you for getting back to me. As requested, please find attached Personality Profiling sample dataset. So, the dataset consist the scores of individual people based on personality factors. Then there is an average score calculation where we want to see what is the average score by each personality factor. However, the personality factor should be in row format and not as column headers to create visuals where every bar in the bar chart is represent a personality factor with its average scoring. Do drop your suggestions and inputs, much appreciated. Link: https://docs.google.com/spreadsheets/d/1MO7w8qsy_BV60zp78NNJjGwEdlJ8W36a/edit?usp=sharing&ouid=11165... 

HotChilli
Super User
Super User

It sounds like you want to Unpivot certain columns.

It's always better to provide some sample data and desired output to get better answers.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors