Forum Discussion

dogburalHK82's avatar
dogburalHK82
Helper III
3 years ago
Solved

merge two tables

Hi, 

 

I have two tables as below

 

Table 1

Part No.   202307    202308

AA11
BB11
CC11

 

Table 2

Part No.    202309   202310

AA11
DD11
EE11

 

Eventually, I would like to merge them and look like below

Part NO.   202307  202308      202309   202310

AA1111
BB11  
CC11  
DD  11
EE  11

 

When i merge them, I only managed to do like below

 

Even column merge, it appears like below

Please advise how I can achieve. 

 

Thanks

 

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    Instead of Joining, you can

    • Combine the two tables
    • Group by Part No.
    • Custom aggregation of each subgroup whereby you "fill up" the columns
      • Return only the first row of the table

     

    let

//Change both Source lines to reflect the actual source of the two tables
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    Table1 = Table.TransformColumnTypes(Source,{{"Part No.", type text}, {"202307", Int64.Type}, {"202308", Int64.Type}}),

    Source2 = Excel.CurrentWorkbook(){[Name="Table_2"]}[Content],
    Table2 = Table.TransformColumnTypes(Source2,{{"Part No.", type text}, {"202309", Int64.Type}, {"202310", Int64.Type}}),

//Combine the two tables
    Combine = Table.Combine({Table1, Table2}),

//Then Group By Part No. and perform custom aggregation
    #"Grouped Rows" = Table.Group(Combine, {"Part No."}, {
        {"All", each Table.FillUp(_, Table.ColumnNames(_)){0}}}),

//Re-expand data and set the data types
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"202307", "202308", "202309", "202310"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"202307", Int64.Type}, {"202308", Int64.Type}, {"202309", Int64.Type}, {"202310", Int64.Type}})
in
    #"Changed Type"

     

     

    Results

     

     

     

     

2 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

    Instead of Joining, you can

    • Combine the two tables
    • Group by Part No.
    • Custom aggregation of each subgroup whereby you "fill up" the columns
      • Return only the first row of the table

     

    let

//Change both Source lines to reflect the actual source of the two tables
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    Table1 = Table.TransformColumnTypes(Source,{{"Part No.", type text}, {"202307", Int64.Type}, {"202308", Int64.Type}}),

    Source2 = Excel.CurrentWorkbook(){[Name="Table_2"]}[Content],
    Table2 = Table.TransformColumnTypes(Source2,{{"Part No.", type text}, {"202309", Int64.Type}, {"202310", Int64.Type}}),

//Combine the two tables
    Combine = Table.Combine({Table1, Table2}),

//Then Group By Part No. and perform custom aggregation
    #"Grouped Rows" = Table.Group(Combine, {"Part No."}, {
        {"All", each Table.FillUp(_, Table.ColumnNames(_)){0}}}),

//Re-expand data and set the data types
    #"Expanded All" = Table.ExpandRecordColumn(#"Grouped Rows", "All", {"202307", "202308", "202309", "202310"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"202307", Int64.Type}, {"202308", Int64.Type}, {"202309", Int64.Type}, {"202310", Int64.Type}})
in
    #"Changed Type"

     

     

    Results