Forum Discussion
Power Query Merge / expand and retain grouping / why is grouping Lost?
Hi,
I sometimes find when I expand a table column after merging I lose grouping , in fact I cant even see a pattern, there must be one? , but If only need one colum I can aggregtte and expand as list this keeps the grouping; so
so two table one colunm ItemL and a two colummn Item Name columns ;
This woul lose the grouping ;
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"ItemL"}, Table1, {"Item"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Name"}, {"Table1.Name"})
in
#"Expanded Table1"This as a list which keeps the grouping ;
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"ItemL"}, Table1, {"Item"}, "Table1", JoinKind.LeftOuter),
#"Aggregated Table1" = Table.AggregateTableColumn(#"Merged Queries", "Table1",
{{"Name", each _ , "Count of Table1.Name"}}),
#"Expanded Count of Table1.Name" = Table.ExpandListColumn(#"Aggregated Table1", "Count of Table1.Name")
in
#"Expanded Count of Table1.Name"also If wrap in buffer prior to expanding the name column the grouping is kept,
Table.Buffer(
Table.NestedJoin(Source, {"ItemL"}, Table1, {"Item"}, "Table1", JoinKind.LeftOuter) )so I'm intereseted in why the grouping is lost ? is there a pattern as to how expand table column works ?
Richard.
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"?
8 Replies
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/1447523I'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.
What do you mean by "grouping"?
i mean grouping, I merge see sample ; A merged to all the A's, but when I expand the
they are are not kept togetherIf I understand correctly, you would like to to a merge where the order of "ItemL" in the first table is preserved.
With Table.NestedJoin the program chooses the Join Algorithm to use, and that will not necessarily provide those results.
However, with "Table.Join" you can choose the Join Algorithm, and the right hash is what you want:
Table.Join(TableA, {"ItemL"}, TableB, {"Item"}, JoinKind.LeftOuter, JoinAlgorithm.RightHash)Results
Whether this will be more efficient than other methods (eg adding an Index column) is something you will need to test using trial and error.
