Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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.
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
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
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)
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.
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 ;
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
61 | |
19 | |
17 | |
13 |