Power Query Merge / expand and retain grouping / why is grouping Lost?

[Dicken]

 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.

[lbendlin · Accepted solution]

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.

[ronrsnfld · Accepted solution]

What do you mean by "grouping"?

[watkinnc · Accepted solution]

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

[ronrsnfld · Accepted solution]

What do you mean by "grouping"?

[Dicken]

 i mean grouping, I merge  see sample ; A merged to all the A's, but when I expand  the 
they are   are not kept together 

[ronrsnfld]

If 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. 

[lbendlin]

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-...

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/1447...

[Dicken]

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 

[lbendlin · Accepted solution]

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.

[Dicken]

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 use 

 Table.ExpandListColumn( 
 Table.AggregateTableColumn(#"Merged Queries", "Table2", {{"Name", each List.Distinct(  _ ), "N"}}), "N")

Richard