Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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.
Solved! Go to Solution.
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"!
Future readers may also want to check out Microsoft's documentation on unpivoting:
https://learn.microsoft.com/en-us/power-query/unpivot-column
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"!
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...
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.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
26 | |
16 | |
15 | |
12 |