Sum values in child table based on two filters

Question

Hi all,&nbsp;&nbsp;I'm pretty new in Power Query and would like to achive the following :&nbsp;I have two tables :Table 1Entry No.1234&nbsp;Table 2Entry No.Cost CodeCost Amount1AA1502BB2002AA1001AA3004CC2001CC4004AA2003BB1504CC150&nbsp;I would like to add three additional columns in table 1 per cost code (manually)&nbsp;Entry No.Cost Code AACost Code BBCost Code CC1450 (150 + 300)04002100200030150042000350 (200 + 150)&nbsp;I don't know how to achive this in Power query.&nbsp; I'm not able two have second table filtered on entry No. and fixed filter value (AA, BB, CC) for each different column.&nbsp;&nbsp;Thanks in advance.&nbsp;Steve

anonymous · Accepted Answer

Hi&nbsp;SteveDesmedt&nbsp;
You can create two blank queries and put the following code to advanced editor in power query
&nbsp;
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0BBKGpgZKsTrRSkZAtpMTkDAyQAhAVEAF4FqMoQImQLazM5IWQ5iACZIKsBaYCmOYLTBr4WaABWIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry No." = _t, #"Cost Code" = _t, #"Cost Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry No.", Int64.Type}, {"Cost Code", type text}, {"Cost Amount", Int64.Type}})
in
    #"Changed Type"let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Entry No." = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entry No.", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Entry No."}, Query1, {"Entry No."}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Cost Code", "Cost Amount"}, {"Cost Code", "Cost Amount"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Query1", List.Distinct(#"Expanded Query1"[#"Cost Code"]), "Cost Code", "Cost Amount",  List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"AA", "BB", "CC"})
in
    #"Replaced Value"
&nbsp;
Output

Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Sum values in child table based on two filters

2 Replies

Related content

Pass value from child to parent pipeline

Parent/child hierarchy creation

Child and parent filter

XML Source - Getting child value with XQuery

Filter

Entry No.	Cost Code	Cost Amount
1	AA	150
2	BB	200
2	AA	100
1	AA	300
4	CC	200
1	CC	400
4	AA	200
3	BB	150
4	CC	150

Entry No.	Cost Code AA	Cost Code BB	Cost Code CC
1	450 (150 + 300)	0	400
2	100	200	0
3	0	150	0
4	200	0	350 (200 + 150)