Search code examples
excelpowerbipowerquerypowerbi-desktopm

Table.Combine after Table.NestedJoin - Table.Expand dynamically with Power Query M

i want to combine two tables after a NestedJoin (Left outer). When I expand the Quali-Matrix manually it will cause problems in the future. So i want to do it dynamically if the colums of the Quali-Matrix changes.

I tried:

let
    Quelle = Table.NestedJoin(Abwesenheit, {"MA"}, #"Quali-Matrix", {"Arbeitsplatzname1"}, "Quali-Matrix", JoinKind.LeftOuter),
    RemoveOthers = Table.SelectColumns(Quelle,{"Quali-Matrix"}),
    out = Table.Combine(RemoveOthers["Quali-Matrix"])
in
    out

This does not work, I does not find Quali-Matrix.

Table at the beginning looks like this:

enter image description here

I expect at the end that the combined tables are dynamically expended.

Thanks!

Solution

  • A different approach is a simply dynamic expand

    #"Merged Queries" = Table.NestedJoin(Part1,{"Custom"},Part2,  {"Custom"},"Part2",JoinKind.FullOuter),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(#"Merged Queries", "Part2"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expanded" = Table.ExpandTableColumn(#"Merged Queries", "Part2",ColumnsToExpand ,ColumnsToExpand ),