Merge 2 tables and create new rows

[afif_hazim]

Dear experts, I am looking for a way to merge or append the Talent Partners table and Month table.

I have 2 tables, table 1 is Talent Partners that have 1 column with list of TP and table 2 is Month with 2 columns that have list of month number and month name.

The goal that I want to achieve is to have a table that have a list of Talent Partners for every month. E.g. If I have 6 Talent Partners, I should have 72 rows with list of months and TP for every month.

I have try to appended and merged both tables month and TP but the result is shown as per below image. Some of the rows created null value instead of merging the table and created new rows of TP with different month.

Your advice is highly appreciated! Thank you.

[v-stephen-msft · Accepted solution]

Hi @afif_hazim ,

In your case, you just add a custom column contains the table and then expand it.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Mahesh0016]

@afif_hazim Please refer below M Code. I hope this helps you. Thank you!!

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMlSK1YlWcktNArKNwGzfxCIg2xjMdiwAsU2g4pVAtimY7VUK0msGZecA2eYQ9aXpQLYFmB2cWgBkW4LZ/sklILsMwBy//DIQB2KzS2oyiAO0OhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month Name" = _t, #"Month Index" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month Name", type text}, {"Month Index", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Partners", each #"Talent Partners"),
#"Expanded Partners" = Table.ExpandTableColumn(#"Added Custom", "Partners", {"TP"}, {"Partners.TP"})
in
#"Expanded Partners"