Forum Discussion
Power Query Merge / expand and retain grouping / why is grouping Lost?
As always the caveat is that you have to validate for yourself which of the many ways to do lookups/joins/merges in Power Query is applicable in your scenario, both from a performance and a scalability perspective. Thankfully we don't have to guess - we can use the query diagnostics to measure and know.
As I said the best join is the one you don't do.
What do you mean by "grouping"?
Power Query merge operations do not guarantee the sort order after the merge, unless you buffer the table before the merge. Even if you sort them before the merge, the order may change. Power query does not like to bring the whole table in-memory, which it has to do with a sort operation.
--Nate
You are generally discouraged from using merges as they are very expensive operations. Instead, let the data model do the work if possible.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
I'm not sure how to upload the table I have in power query the expected out come is the grouping is kept after expanding the table column, this is the data ;
then I merge table ItemL to table b Item and exapand the name column but the grouping dissappears,
unless I wrap in table.buffer first or useTable.ExpandListColumn( Table.AggregateTableColumn(#"Merged Queries", "Table2", {{"Name", each List.Distinct( _ ), "N"}}), "N")Richard
I tend not to use it as I just don't like it, I also try and keep pivot / unpivot to a minimum, I was just curious as to why it was happening, for example if i use ;
Table.AddColumn(#"Changed Type", "Custom", (A)=> Table.SelectRows( TestTable, (B)=> B[Item] = A[ItemL] ) )and expand the table Name column or whatever the problem doesn't occur, hte annoying part was I could not see any pattern as to how it was expanding,
Richar.d
As always the caveat is that you have to validate for yourself which of the many ways to do lookups/joins/merges in Power Query is applicable in your scenario, both from a performance and a scalability perspective. Thankfully we don't have to guess - we can use the query diagnostics to measure and know.
As I said the best join is the one you don't do.
