Apr 05 2018
06:17 PM
- last edited on
Jul 12 2019
11:06 AM
by
TechCommunityAP
Apr 05 2018
06:17 PM
- last edited on
Jul 12 2019
11:06 AM
by
TechCommunityAP
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}})"
Apr 06 2018 04:03 PM
Solution
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
Apr 07 2018 11:06 AM
Mar 22 2020 08:42 PM
@Wyn Hopkins Love in the time of corona! thank you.
Aug 18 2021 02:47 PM
Feb 16 2022 10:18 AM
Apr 06 2018 04:03 PM
Solution
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