Power BI - Merge queries as new - More than 2 tables

[Anonymous]

Hello,

Is there any feature in Power BI making it possible to merge quickly various tables with a left join ?

Example

Table A contains [ID] and [Field_1]

Table B contains [ID] and [Field_2]

Table C contains [ID] and [Field_3]

Table D contains [ID] and [Field_4]

Table E contains [ID] and [Field_5]

I want a table F containing those [ID] and the 5 fields.

At the moment I know only one solution: Merge A and B into a first table, then merge this new table with C, and then with D and then with E.

Thanks.

[ImkeF · Accepted solution]

There might be an easier solution to this, but this is all I can come up with currently:

let
    TableA = #table({"ID", "Column1"}, {{"A", 10}, {"B", 20}}),
    TableB = #table({"ID", "Column2"}, {{"A", 100}, {"B", 200}}),
    TableC = #table({"ID", "Column3"}, {{"A", 1000}, {"B", 2000}}),
    ListOfTables = {TableA, TableB, TableC},
    DynamicLeftOuterJoin = List.Accumulate(List.Skip(ListOfTables,1),
                            [x = ListOfTables{0}, Counter=0], 
                            (state, current) => [x = let 
                                                        Join = Table.NestedJoin(state[x],{"ID"},current,{"ID"},"Result" & Text.From(state[Counter]),JoinKind.LeftOuter), 
                                                        Expand = Table.ExpandTableColumn(Join, "Result" & Text.From(state[Counter]), {List.Difference(Table.ColumnNames(current), {"ID"}){0}}
                                                        )
                                                    in 
                                                        Expand,
                                                Counter = state[Counter]+1])[x]
in
    DynamicLeftOuterJoin