insert rows from other table for each unique entry in a column of first table

Hi d_sa,

here's a not too sophisticated solution to your challenge but hopefully it works for you. I'm pretty sure it could be done in a more elegant way.

You can try to paste this code in the advanced editor in PQ to see it in action:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDQAgCETRXagtFEVdgs7OuP8acjaozU94AeakRIEGoojQCrexJUVHzJqB8mGx1GP8fGxuuNOKY/6wAYsjnmm32OLa", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"M Type" = _t, Material = _t, #"Prod Qtty" = _t]),
    tbl2 = #table({"M Type"}, {{"T1"}, {"T2"}, {"T3"}, {"T4"}, {"T5"}}),
    #"Grouped Rows" = Table.Group(Source, {"Month"}, {{"aaa", each _, type table [Month=nullable number, M Type=nullable text, Material=nullable text, Prod Qtty=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FillDown(Table.Combine({[aaa], Table.AddColumn(tbl2, "Material", each [M Type]) }), {"Month"})),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Month", "M Type", "Material", "Prod Qtty"}, {"Month", "M Type", "Material", "Prod Qtty"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",null,"0",Replacer.ReplaceValue,{"Prod Qtty"})
in
    #"Replaced Value"

That should be an output table:

Same results as final table. Don't know if the algorithm is entirely correct:

Here's the code - some tables stolen from @Resolver III :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDQAgCETRXagtFEVdgs7OuP8acjaozU94AeakRIEGoojQCrexJUVHzJqB8mGx1GP8fGxuuNOKY/6wAYsjnmm32OLa", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"M Type" = _t, Material = _t, #"Prod Qtty" = _t]),
    SourceCT = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Prod Qtty", type number}}),
    tbl2 = #table({"M Type"}, {{"T1"}, {"T2"}, {"T3"}, {"T4"}, {"T5"}}),
    Qry3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQoBEYZKsTpQrhGQMEJwjeGyRjDFRgiuEaqsCYQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"M Type" = _t, #"Scrap Qtty" = _t]),
    Qry3CT = Table.TransformColumnTypes(Qry3,{{"Month", Int64.Type}, {"Scrap Qtty", type number}}),
    DuplicatedColumn = Table.DuplicateColumn(tbl2, "M Type", "Material"),
    #"Grouped Rows" = Table.Group(SourceCT, {"Month"}, {{"grp", each _, type table [Month=nullable number, M Type=nullable text, Material=nullable text, Prod Qtty=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [grp] & DuplicatedColumn),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"grp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"M Type", "Material", "Prod Qtty"}, {"M Type", "Material", "Prod Qtty"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if [M Type]=[Material] then [M Type] else null),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Month", "Custom"}, Qry3CT, {"Month", "M Type"}, "Qry3", JoinKind.LeftOuter),
    #"Expanded Qry3" = Table.ExpandTableColumn(#"Merged Queries", "Qry3", {"Scrap Qtty"}, {"Scrap Qtty"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Qry3",{"Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1",null,0,Replacer.ReplaceValue,{"Prod Qtty", "Scrap Qtty"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Month", Order.Ascending}, {"M Type", Order.Ascending}})
in
    #"Sorted Rows"