Can I join tables from a column of tables based on a different column?

[smann]

Sorry for the titlegore! Here's the situation:

I want to start with something like this:

TableType	TableColumn
1	Table
1	Table
2	Table
2	Table
2	Table
3	Table
3	Table

And my desired result is this:

TableType	JoinedTableColumn
1	Table
2	Table
3	Table

...Where the final tables are joined 1+1, 2+2+2, 3+3.

My instinct says iterate through the list, if [TableType]{Counter} = [TableType]{Counter-1} then join Table to previous Table, else Table. The problem is if I use something like List.Generate, I don't have a way of storing previous table from the last run-through? 

Again, very new to M. Any thoughts are appreciated, thanks!

[parry2k]

are you removing duplicate rows? Is that what you want?

[smann]

@parry2k sorry it wasn't clear. I'm trying to join the tables together. For example, there are 3 tables of TableType=2. I'd like to join all 3 of these tables, and that's the merged table that's listed next to TableType=2 in the final table.

[MarcelBeug]

You can group on TableType with a dummy operation for TableColumn (e.g. minimum).

After the code is generated, adjust it to combine the tables:

= Table.Group(PreviousStep, {"TableType"}, {{"JoinedTableColumn", each Table.Combine([TableColumn]), type table}})

[smann]

@MarcelBeug thanks for your help again. This is a very helpful function, and I might get this to do what I need. It would be better if it were a join rather than a combine function, however -- is there any chance I could achieve that? I tried this (below) but because the tables are separated by primary keys in the join function it doesn't want to run as I've written it:

= Table.Group(PreviousStep, {"TableType"}, {{"JoinedTableColumn", each Table.Join([TableColumn], "PrimaryKey"), type table}})

[smann]

= Table.Group(PreviousStep, {"TableType"}, {{"JoinedTableColumn", each Table.Join([TableColumn], "PrimaryKey"), type table}})

[WolfBiber]

Hey,

are you sure you want a join? Maybe append is the right solution if you want to generate a big table from 2 Source Tables.

Or I misunderstood you 🙂

Greetings,

Wolf

[smann]

@WolfBiber I know it sounds crazy, but I need to do a join. The purpose is to compare these three tables -- ultimately, I will complicate the situation further (in some cases I will want an inner join, in some cases other types of joins), but append/combine will never be the goal.

[WolfBiber]

Ok, so maybe it would be better to generate a surrogate Key to benefit from a solid Data Model in Powerbi. My eperience is: 

Keep the complexity out of the datamodel. Sorry, I'm a traditional BI developer 🙂

But without understanding you goal its hard to help further

Good luck

Greetings,

Wolf

[WolfBiber]

1.) Append tables

2.) have a look to following post

https://community.powerbi.com/t5/Desktop/Is-it-possible-to-use-multiple-field-joins-in-a-table/td-p/...

[MarcelBeug]

Joining tables should be possible using List.Accumulate.

But it will be more complicated; maybe I can take a further look tomorrow Central European Time (UTC + 1),

[MarcelBeug · Accepted solution]

@WolfBiber isn't Power Query the part before the datamodel?

Below 2 queries and a video to show how it looks like. Tables are joined and expanded.

Remark: the tables that are joined, must have different column names (except for "PrimaryKey").

Should you have further requirements, please provide a link to a file with representative test data.

Query Start:

let
    Source = #table(type table[TableType = number,TableColumn = table,joinKind = number],
    {{1,Table1,null},
     {1,Table2,JoinKind.Inner},
     {2,Table3,null},
     {2,Table4,JoinKind.FullOuter},
     {2,Table5,JoinKind.FullOuter},
     {3,Table6,null},
     {3,Table7,JoinKind.FullOuter}})
in
    Source

Query Result:

let
    Source = Table.Buffer(Start),

    JoinTables = (Table as table) as table =>
    let
        RemovedTableType = Table.RemoveColumns(Table,{"TableType"}),
        TableRows = Table.ToRows(RemovedTableType),
        JoinTables = List.Accumulate(List.Skip(TableRows),TableRows{0}{0},(jt,t) => 
        let
            Joined = Table.NestedJoin(jt,{"PrimaryKey"},t{0},{"PrimaryKey"},"JoinedTableColumn",t{1}),
            ColumnNames = List.Difference(Table.ColumnNames(t{0}),{"PrimaryKey"}),
            Expanded = Table.ExpandTableColumn(Joined,"JoinedTableColumn",ColumnNames)
        in
            Expanded)    
    in
        JoinTables,

    #"Grouped Rows" = Table.Group(Source, {"TableType"}, {{"AllRows", JoinTables, type table}})
in
    #"Grouped Rows"

Video:

[smann]

@MarcelBeug I just noticed that in your example, PrimaryKey is being lost when you perform a FullOuter for TableType=2.  Any thoughts on that? I am having trouble attempting to debug it because this is a bit out of my knowledge level of M and I don't totally follow how the JoinTables code works.

[MarcelBeug]

Adjusted code to also expand the PrimaryKey of the nested tables.

In order to prevent name conflicts, an index column is added (and changed to text), and the PrimaryKey columns are expanded as PrimaryKey1, PrimaryKey2, etcetera.

Null values in the PrimaryKey field are replaced with the values from PrimaryKeyn, after which the PrimaryKeyn column is removed.

let
    Source = Table.Buffer(Start),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Index", type text}}),
    JoinTables = (Table as table) as table =>
    let
        RemovedTableType = Table.RemoveColumns(Table,{"TableType"}),
        TableRows = Table.ToRows(RemovedTableType),
        JoinTables = List.Accumulate(List.Skip(TableRows),TableRows{0}{0},(jt,t) => 
        let
            Joined = Table.NestedJoin(jt,{"PrimaryKey"},t{0},{"PrimaryKey"},"JoinedTableColumn",t{1}),
            ColumnNames = List.Difference(Table.ColumnNames(t{0}),{"PrimaryKey"}),
            Expanded = Table.ExpandTableColumn(Joined,"JoinedTableColumn",{"PrimaryKey"}&ColumnNames,{"PrimaryKey"&t{2}}&ColumnNames),
            CopyKey = Table.ReplaceValue(Expanded,null,each Record.Field(_,"PrimaryKey"&t{2}),Replacer.ReplaceValue,{"PrimaryKey"}),
            RemoveKey = Table.RemoveColumns(CopyKey,"PrimaryKey"&t{2})
        in
            RemoveKey)    
    in
        JoinTables,

    #"Grouped Rows" = Table.Group(#"Changed Type", {"TableType"}, {{"AllRows", JoinTables, type table}})
in
    #"Grouped Rows"

[WolfBiber]

@MarcelBeug I think it is the tool to build the datamodel. But my approach is always to build and keep it as simple as possible (coming from MS SQL World SSIS, SSAS TSQL etc.) : it is easier to mantain, explain and reuse and often if youre using very complex models you have to consider to much in further calculations. But anyway, your solution is great and I'm always pleased to learn new ways to accomplish such tasks from people like you.

[smann]

@MarcelBeug This is working! Thank you so much for your help I appreciate it.