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 AA 1 1 BB 1 1 CC 1 1   Table 2 Part No.    202309   202310 AA 1 1 DD 1 1 EE 1 1 ...
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