SOLVED

Power Query Table.Group Doesn't Keep Sort

Copper Contributor

I'm new to Power BI, so any help would be appreciated.  I've gotten pretty far in my query building, but I'm stuck.  Essentially I wanted to create a unique Index based on "PHN Combo" (i.e. PHN Combo A 1,2,3, PHN Combo B 1,2, etc.).  However I wanted it to be sorted by "Birth Date."  Unfortunately when you create the Table.group it ignores the previous sort.  So I tried to add a "Each Table.Sort" to my query, and it didn't work.  I know I did something wrong (because I have no idea what i'm doing).  Can someone help me please?  Thanks 

 

Here's my formula:

 

"= Table.Group(#"Sorted Rows1", {"PHN Combo"}, each Table.Sort(_,"Birth Date", Order.Ascending), {{"Count", each Table.AddIndexColumn(_, "Index", 1,1), type table}})"

5 Replies
best response confirmed by Adam Underwood (Copper Contributor)
Solution

Hi @Adam Underwood

 

 

I seem to remember coming across this in the past and  the "trick" was to add a normal index column after doing the sort,  then after grouping you can delete the index column

 

Adding the index step seems to force Power Query to keep the sort order

 

Give it a go and let us know how you get on

You beautiful person...It worked! thank you!

@Wyn Hopkins Love in the time of corona! thank you.

Very helpful tip! Worked beautifully.
Seems to have worked for me! Thank you!
1 best response

Accepted Solutions
best response confirmed by Adam Underwood (Copper Contributor)
Solution

Hi @Adam Underwood

 

 

I seem to remember coming across this in the past and  the "trick" was to add a normal index column after doing the sort,  then after grouping you can delete the index column

 

Adding the index step seems to force Power Query to keep the sort order

 

Give it a go and let us know how you get on

View solution in original post