cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Galven Frequent Visitor
Frequent Visitor

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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




1 REPLY 1
Highlighted
Super User
Super User

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

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries