Subtotals and Sort

[Mederic]

Hello everyone,
I tried to reproduce the exercise in the video in this link.
The problem is that in my case, the sorting is done in the Product column but not in the other columns.
For example, the Season column is no longer in the same order as the initial table
Thank you in advance for your solutions

Best regards

Below is the initial table, the code used in the video and the result I obtained :

Product	Season	Region 1	Region 2
A	1	50	50
A	2	14	13
A	3	36	38
A	4	23	36
B	1	45	26
B	2	31	12
B	3	49	48
B	1	41	39
C	1	26	25
C	2	22	49
C	3	35	39
C	4	25	27
D	1	26	27
D	2	16	29
D	3	47	48
D	4	43	18

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MainTable = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Season", Int64.Type}, {"Region 1", Int64.Type}, {"Region 2", Int64.Type}}),
    Subtotals = Table.TransformColumns(Table.Group(MainTable, {"Product"}, {{"Region 1", each List.Sum([Region 1]), type nullable number}, {"Region 2", each List.Sum([Region 2]), type nullable number}}), {{"Product", each "Total " & _, type text}}),
    GrandTotal = Table.Group(Subtotals, {}, {{"Region 1", each List.Sum([Region 1]), type nullable number}, {"Region 2", each List.Sum([Region 2]), type nullable number}, {"Product", each "Grand Total", Int64.Type}}),
    Custom1 = MainTable & Subtotals & GrandTotal,
    #"Added Custom" = Table.AddColumn(Custom1, "Region Total", each [Region 1]+[Region 2]),
    #"Sorted Rows" = Table.Sort(#"Added Custom", each Text.Reverse([Product]))
in
    #"Sorted Rows"

[Mederic]

@ronrsnfld , @lbendlin ,

I've just tested it in Office 365 and no problem, the sorting is preserved (see screenshot below).
So it depends on the version of Excel
Have a nice evening

[v-aatheeque]

Hi @Mederic ,

Glad that your query got resolved, please mark the helpful reply and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster .

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[ronrsnfld · Accepted solution]

Although I could not reproduce your problem with your data and your M-Code, Table.Group is not guaranteed to keep the original sort order.

So I cannot test this workaround. However, I would try adding an Index column after the Main Table step (and deleting after the last step).

[lbendlin]

ok, but why?  Sorting in Power Query is largely pointless as the sort order will be ignored once the data is loaded into Power BI. 

Grouping is ok but it comes with a loss of detail.

These things should be done in Power BI, not in Power Query.

[Mederic]

Hello everyone,

Thank you for your answers,

@lbendlin , it's just an exercise, I'm surprised that in the video the sorting is maintained and in my case the order is no longer the same.

@ronrsnfld , I've tried it with an Index column too, but I can't get the desired result. I've probably got it wrong.

Also, in my 1st file, I didn't have the same "Season" numbers as in the video. I've just corrected this column
The test file can be found at this link

Thanks in advance
Best regards

[ronrsnfld]

In your uploaded file, I find:

1. The worksheet table is incorrectly sorted as you show when I first open it.
2. Both tables in Power Query are correctly sorted.
3. Refreshing the table in the worksheet results in it showing the correct sort order on the worksheet.

I am at a loss since I cannot reproduce your issue.

Although, as @lbendlin pointed out, the sort order may not be retained in Power BI, I've never seen that happen in Excel.

[Mederic]

Thanks @ronrsnfld 
Strange indeed,

I use the Excel 2019 Pro version,
I'll try it tomorrow on the Office 365 work version to see the result.
Best regards