Merging multiple tables in one step

[cmajka]

Hi -

Is it possible to merge multiple tables to the same table in one step?

My original code looked like this:

= Table.NestedJoin(#"Expanded NewColumn",{"BI_Lim"},BILIMIT,{"ORIGINAL"},"NewColumn",JoinKind.LeftOuter)

but what if I wanted to join another table in the same step...something like this:

= Table.NestedJoin(#"Expanded NewColumn",{"BI_Lim"},BILIMIT,{"ORIGINAL"},"NewColumn",JoinKind.LeftOuter),",{"PD_Lim"},PDLIMIT,{"ORIGINAL"},"NewColumn",JoinKind.LeftOuter)

However, this doesn't seem to work. Am I missing something in the syntax? I have about 15 'lookup' tables that I would like to merge with my main table, and this would save a lot of repetitive steps.

Thanks!

 

[Rudi]

I'm certainly not an expert on PQ, but I doubt that is possible.

Since the step changes the table structure by adding an additional column, nesting another NestedJoin function into the outer one will need to refer to the same previous step which effectively is altered by the inside step.

Besides this, once you have merged a table the column still needs to be expanded which further complicates the process.

Since you have about 15 tables, (and in my honest opinion), keeping these steps separated will be far more advisable and easier to edit if further modifications need to be made to the code.

Some steps can logically be combined into a nested state to shorten the 'M' code, but this function is not suitable for this process.

Cheers

 

[cmajka]

Thanks for the reply Rudi. What you say logically makes sense. For now, I've left it as is, and it works great. However, I'll keep searching for a more efficient way. Thank again!

 

[MarcelBeug]

List.Accumulate may come to the rescue.
A simple example below with 3 tables that will only work if there is 1 key field and all tables have unique fieldnames (otherwise the code must be adjusted to make the field names unique).

You need a list with the tables to be merged and the name of their key column, e.g. MergeList:

= {{Table2,{"Key2"}},{Table3,{"Key3"}}}

The key column is in list format like in Table.NestedJoin.

The code to multiple merge the tables:

let
    Source = Table1,
    MultipleMerge = List.Accumulate(MergeList,Source,(MergesSoFar,Current) => 
        let
            #"Merged Queries" = Table.NestedJoin(MergesSoFar,{"Key1"},Current{0},Current{1},"MarcelBeugsJoinColumn",JoinKind.LeftOuter),
            #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "MarcelBeugsJoinColumn", List.Skip(Table.ColumnNames(Current{0})))
        in
            #"Expanded Table")
in
    MultipleMerge