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

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

Sorry for the titlegore! Here's the situation:

 

I want to start with something like this:

TableTypeTableColumn
1Table
1Table
2Table
2Table
2Table
3Table
3Table

 

And my desired result is this:

TableTypeJoinedTableColumn
1Table
2Table
3Table

 

...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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

@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:

Specializing in Power Query Formula Language (M)
14 REPLIES 14
Super User
Super User

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

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




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

Proud to be a Datanaut! Connect with me on Linkedin






smann Frequent Visitor
Frequent Visitor

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

@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.

Super User
Super User

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

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}})
Specializing in Power Query Formula Language (M)
smann Frequent Visitor
Frequent Visitor

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

@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}})
 
WolfBiber Member
Member

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

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 Smiley Happy

 

Greetings,

Wolf

smann Frequent Visitor
Frequent Visitor

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

@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 Member
Member

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

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 Smiley Happy

 

But without understanding you goal its hard to help further

 

Good luck

Greetings,

Wolf

WolfBiber Member
Member

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

Super User
Super User

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

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),

Specializing in Power Query Formula Language (M)