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

[d_sa]

Hello all,

First of all, I'm pretty new to power query/pbi, so this may be a bit of a dumb question, but I searched and couldn't find an answer, so here it goes:

I have loaded 3 queries. 1st one merges several monthly files. Simplified, it has columns A for month, another one B with material type, another one with C materials and one D with production quantity1

	A	B	C	D
1	Month	M Type	Material	Prod Qtty
2	1	T1	M1	5
3	1	T1	M2	10
4	1	T2	M3	15
5	1	T2	M4	6
6	2	T1	M1	7
7	2	T3	M6	12
8	2	T3	M7	14
9	2	T4	M8	2

Then I have a second table with column A containing all the material types (B in table 1)

	A
1	M Type
2	T1
3	T2
4	T3
5	T4
6	T5

Finally a third query (again monthly files) with table with A month, B material type (B in table 1), and C scrap quantity (i cant relate scrap to materials, only to material types, at least at this point)

	A	B	C
1	Month	M Type	Scrap Qtty
2	1	T1	1
3	1	T2	2
4	1	T3	1
5	2	T1	2
6	2	T2	1
7	2	T4	1

I'd like to merge query 2 to query one, adding all the material types as rows in the first query for each month (all T1 to T5 even if they're zero), repeating material type as material in column C and quantity produced as zero. Then i'd get scrap quantity from query 3, but this i'd figure out easily, my issue is in adding all the lines from table 2 for each unique entry in table 1/column A (Final output expected below)

Thank you in advance!

	A	B	C	D	E
1	Month	M Type	Material	Prod Qtty	Scrap Qtty
2	1	T1	M1	5	0
3	1	T1	M2	10	0
4	1	T1	T1	0	1
5	1	T2	M3	15	0
6	1	T2	M4	6	0
7	1	T2	T2	0	2
8	1	T3	T3	0	1
9	1	T4	T4	0	0
10	1	T5	T5	0	0
11	2	T1	M1	7	0
12	2	T1	T1	0	2
13	2	T2	T2	0	1
14	2	T3	M6	12	0
15	2	T3	M7	14	0
16	2	T3	T3	0	0
17	2	T4	M8	2	0
18	2	T4	T4	0	1
19	2	T5	T5	0	0

[wini_R · Accepted solution]

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:

[p45cal · Accepted solution]

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"

[wini_R · Accepted solution]

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: