none
Joining together a list of tables? RRS feed

  • Question

  • Is there a simple way to join a list of tables? I'm comfortable with DAX so coding it in the advanced editor would be fine.

    In a procedural programming language I would do some sort of "for" loop over the list. I'm not sure the equivalent in DAX.

    To be clear, I'm not talking about concatenating tables (Table.Combine), I'm talking about a left outer join across them (Table.Join).

    Friday, November 1, 2019 6:48 PM

All replies

  • Hi Texas Ed,

    do the key-columns (to join on)  of your tables all have the same name?

    BTW: In the query editor (in Power Query) you use a different language to DAX which is called "M".


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, November 2, 2019 6:58 AM
    Moderator
  • Hello Texas Ed

    you can try to use List.Generate, but as Imke already stated this would only work when column names are different and the key-columns have to be the same. See example below

    let
        Quelle = {#table({"Index", "Valuetbla"}, {{1,"a"}, {2, "b"}, {3,"c"}}),#table({"Index", "Valuetblb"}, {{1,"d"}, {2, "e"}, {3,"f"}}), #table({"Index", "Valuetblc"}, {{1,"g"}, {2, "h"}, {3,"i"}})},
        FinalTableJoin = List.Generate(()=> [ x = 0 , y = Quelle{0}] , each [x] < List.Count(Quelle) , each [x = [x] + 1, y = Table.Join([y], "Index", Quelle{[x]+1}, "Index")], each [y]),
        GetFinalTable = FinalTableJoin{List.Count(FinalTableJoin)-1}
    in
        GetFinalTable


    Query it

    • Proposed as answer by Lz._ Wednesday, November 20, 2019 12:39 PM
    Saturday, November 2, 2019 9:53 AM
  • Tried now to create a dynamic solution. Only works if index column is always the first column a every column name has to be unique, then you can apply a JoinKind.FullOuter

    let
        Quelle = {#table({"Indextbla", "Valuetbla"}, {{1,"a"}, {2, "b"}, {3,"c"}}),#table({"Indextblb", "Valuetblb"}, {{1,"d"}, {2, "e"}, {3,"f"}, {6, "c"}}), #table({"Indextblc", "Valuetblc"}, {{1,"g"}, {2, "h"}, {3,"i"}})},
        FinalTableJoin = List.Generate(()=> [ x = 0 , y = Quelle{0}] , each [x] <= List.Count(Quelle)-1 , each [x = [x] + 1, y = Table.Join([y], Table.ColumnNames([y]){0}, Quelle{[x]+1}, Table.ColumnNames(Quelle{[x]+1}){0}, JoinKind.FullOuter)], each [y]),
        GetFinalTable = FinalTableJoin{List.Count(FinalTableJoin)-1}
    in
        GetFinalTable


    Query it

    Sunday, November 3, 2019 1:59 PM
  • Hello

    did my proposed solution work out?


    Query it

    Thursday, November 7, 2019 5:51 PM
  • Other option with List.Accumulate

    1 - Key-columns have the same name

    let
        Source =
            {
                #table(type table[KeyCol=Int64.Type, Other1=Text.Type], List.Zip({{1..5},{"a".."e"}})),
                #table(type table[KeyCol=Int64.Type, Other2=Text.Type], List.Zip({{1..4},{"f".."i"}})),
                #table(type table[KeyCol=Int64.Type, Other3=Text.Type], List.Zip({{1..6},{"j".."o"}}))
            },
        JoinedTables = List.Accumulate(List.Skip(Source), Source{0}, (state,current)=>
            Table.Join(state,"KeyCol", current,"KeyCol")
        )
    in
        JoinedTables

    2 - Key-columns have a different name and are always 1st column

    let
        Source =
            {
                #table(type table[KeyCol1=Int64.Type, Other1=Text.Type], List.Zip({{1..5},{"a".."e"}})),
                #table(type table[KeyCol2=Int64.Type, Other2=Text.Type], List.Zip({{1..4},{"f".."i"}})),
                #table(type table[KeyCol3=Int64.Type, Other3=Text.Type], List.Zip({{1..6},{"j".."o"}}))
            },
        JoinedTables = List.Accumulate(List.Skip(Source), [iTable=Source{0}], (state,current)=>
            [
                curIdxName = Table.ColumnNames(current){0},
                iTable = Table.RemoveColumns(
                    Table.Join(state[iTable],Table.ColumnNames(state[iTable]){0}, current,curIdxName),
                    {curIdxName}
                )
            ]
        )[iTable]
    in
        JoinedTables


    Friday, November 8, 2019 1:33 PM