Forum Discussion
Transposing Schedules From Column to Rows
Hi, I've tried a couple of pivoting approaches but I can't seem to get the desired outcome.
I would like to transpose the data from the left to the right using Power Query. Note that the columns will be dynamic as schedules will be added weekly. I appreciate any help you can provide.
Click on ID, NAME in power query editor and right click --> unpivot other columns. so even though your other columns are dynamic it can pickup
let Source = your source , #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}, {"Value", "Schedule"}}) in #"Renamed Columns"
4 Replies
Transposing data from columns to rows in Power Query can be accomplished using the unpivot transformation. If you have a table where you want to transpose the data from columns to rows and you're working with Power BI, here's a step-by-step guide:
Steps to Transpose Columns to Rows in Power BI using Power Query:
Load your data into Power BI:
- Make sure your data is loaded into Power BI's data model.
Open Power Query Editor:
- Click on the "Edit Queries" button on the Home tab of the Power BI Desktop.
Select the Columns to Unpivot:
- In the Power Query Editor, select the columns you want to transpose from columns to rows.
Unpivot Columns:
- With the selected columns highlighted, go to the "Transform" tab in the Power Query Editor.
- Click on the "Unpivot Columns" option. This will transform your columns into attribute-value pairs.
Rename Columns:
- After unpivoting, you'll have new columns named "Attribute" and "Value." Rename these columns as per your preference. For instance, if you unpivoted dates, "Attribute" could be renamed to "Date" and "Value" to "Schedule".
Close & Apply:
- Once you've made the necessary transformations, click on the "Close & Apply" button in the Power Query Editor to apply the changes and load the data back into Power BI.
Handling Dynamic Columns:
If you mentioned that columns will be dynamic (added weekly), you might want to create a dynamic approach where new columns are automatically processed.
Ensure Consistent Naming: Make sure that the naming pattern for the columns you want to unpivot remains consistent. For example, if new schedules are added weekly with names like "Schedule_Week1", "Schedule_Week2", and so on, the approach above will capture these as they're part of the pattern.
Refresh Data Regularly: Since your columns are dynamic, ensure that your data source is refreshed regularly so that Power BI picks up the new columns and applies the transformations automatically.
By following these steps, you should be able to transpose data from columns to rows in Power BI using Power Query, even when dealing with dynamic columns.
Hi ebp2022
Please share a source table in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...Click on ID, NAME in power query editor and right click --> unpivot other columns. so even though your other columns are dynamic it can pickup
let Source = your source , #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID", "Name"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}, {"Value", "Schedule"}}) in #"Renamed Columns"This worked, thank you!
