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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors