Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Dicken
Helper IV
Helper IV

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.



3 ACCEPTED SOLUTIONS

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.

View solution in original post

ronrsnfld
Super User
Super User

What do you mean by "grouping"?

View solution in original post

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

8 REPLIES 8
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
ronrsnfld
Super User
Super User

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 together 

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)

 

 

 

ronrsnfld_1-1723570072602.png

Results

ronrsnfld_0-1723569947715.png

 

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
Super User
Super User

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

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.

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 ;

Dicken_0-1723397370306.png

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 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.