Log In
Register
Home
February 20, 2019
Offline
I use Group By to combine kids' first names. I hope to accomplish it by combining of first names in ascending order such that:
Chloe & Charlotte---> Charlotte & Chloe
Cody & Benjamin & Abigail---> Abigail & Benjamin & Cody
Dessiah & Sinai & Antonio---> Antonio & Dessiah & Sinai
Stella & Liliana & Logan--> Liliana & Logan & Stella
I did sort them before I group them, but I don't know why the results are not what I expected.
Appreciate your time and help.
JIm
Moderators
January 31, 2022
Offline
Hi Jim,
Have a look at the attached file. I 've added a query that does what you need. Key in this solution is to sort the children's first names and wrap the step in a Table.Buffer function. That fixes the sorted table in memory. Then you simply group by Customer and Text.Combine by First1. Just two steps after connecting to the Source.
Riny
February 20, 2019
Offline
Riny,
It works great. Thanks.
I Googled it and it seems to me that PowerQuery has a tendency to try and be too efficient, in this case, removing a bunch of rows and sorting the smaller dataset. We add Table.Buffer to force PowerQuery to actually sort the whole table.
Is my understanding correct?
Moderators
January 31, 2022
Offline
Not sure what you mean by "PQ trying to be too efficient". As in Excel, there are many ways to achieve the same end result. Some are more efficient than others. Table.Buffer is needed, for instance, when you notice that sorting goes wrong. More about that in the link below.
1 Guest(s)
