merge two tables

[dogburalHK82]

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

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

Part NO.   202307  202308      202309   202310

AA	1	1	1	1
BB	1	1
CC	1	1
DD			1	1
EE			1	1

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

[dogburalHK82]

@ronrsnfld Thank you very  much.

[ronrsnfld · Accepted solution]

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