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.
Hello - This forum is great, I have learned so much in the last few months from users here!
I'm relatively new to PowerBI, and iIf this topic already exists, I apologize, perhaps I was not using correct terminology when searching, but I couldn't find anything.
I am trying to create a particular view in a data table /query to mimic an output currently created by hand in Excel pivot.
(I know how to do this in a visual, but can't get the visual to export in the same format I created, so I am using a Data Table instead because it can be copied & pasted into another Excel, PowerPoint, etc. in the same format)
I successfully copied my original table and grouped it by 2 fields (Line Type & 9Box), and then pivoted to move my Fiscal Year Week spend $ data into individual columns. However, there seems to be no option to select how those columns are ordered after the pivot. It automatically put them in DEscending order (newest week to oldest week column), but I would rather have oldest to newest, so that future week columns of spend data (when source data is refreshed) are correctly placed at the end/right of the data.
Is there any way I can force Power BI to Pivot the columns in a different order or sort the order AScending after my pivot? i.e start with column "FY20 01" and run to column "FY20 21" from left to right?
Here's the synthax from the advanced editor if that is helpful:
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Fiscal Calendar.Fiscal Week"]), "Fiscal Calendar.Fiscal Week", "PO Value by 9Box by Line Type"),
Any help would be appreciated, thanks.
V.
Maybe you could supply some data so that we might walk through your steps
You may want to read this post to get your question answered more quickly:
Get your questions answered quickly
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C , thanks for the response.
Here's some sample data - I'm looking to order column 3 to 23 in opposite order from FY20 1 to FY20 20 left to right.
[PO] Line Type | 9 Box BU | FY20 21 | FY20 20 | FY20 19 | FY20 18 | FY20 17 | FY20 16 | FY20 15 | FY20 14 | FY20 13 | FY20 12 | FY20 11 | FY20 10 | FY20 09 | FY20 08 | FY20 07 | FY20 06 | FY20 05 | FY20 04 | FY20 03 | FY20 02 | FY20 01 |
Catalog Item | BU1 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 2 |
Catalog Item | BU2 | 3 | 1 | 2 | 4 | 2 | 3 | 1 | 1 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 2 | 3 |
Catalog Item | BU3 | 1 | 2 | 4 | 2 | 4 | 3 | 4 | 3 | 1 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 4 |
Catalog Item | BU4 | 4 | 3 | 1 | 3 | 1 | 2 | 3 | 1 | 3 | 1 | 2 | 1 | 2 | 4 | 2 | 3 | 1 | 3 | 1 | 4 | 2 |
Catalog Item | BU5 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 3 | 1 | 1 | 2 | 3 | 1 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 2 |
Non-Catalog Item | BU1 | 1 | 2 | 4 | 2 | 4 | 3 | 4 | 3 | 1 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 3 | 1 |
Non-Catalog Item | BU2 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 2 |
Non-Catalog Item | BU3 | 4 | 3 | 1 | 3 | 1 | 2 | 3 | 1 | 3 | 1 | 2 | 1 | 2 | 4 | 2 | 3 | 1 | 3 | 1 | 4 | 2 |
Non-Catalog Item | BU4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 | 2 | 2 | 4 | 2 | 4 | 3 | 4 |
Non-Catalog Item | BU5 | 2 | 4 | 2 | 4 | 3 | 4 | 3 | 1 | 4 | 2 | 1 | 2 | 4 | 2 | 4 | 3 | 1 | 2 | 4 | 2 | 4 |
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |