Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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"
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!
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-...
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:
Load your data into Power BI:
Open Power Query Editor:
Select the Columns to Unpivot:
Unpivot Columns:
Rename Columns:
Close & Apply:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
142 | |
104 | |
104 | |
80 | |
66 |