Sort order of Pivoted Columns in Data Table/Query left to right?

[Vanessa_S]

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.

[Nathaniel_C]

@Vanessa_S ,

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

[Vanessa_S]

@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