Forum Discussion
Subtotals and Sort
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).
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.
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
In your uploaded file, I find:
- The worksheet table is incorrectly sorted as you show when I first open it.
- Both tables in Power Query are correctly sorted.
- 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.