Sum values in child table based on two filters

Hi SteveDesmedt 

You can create two blank queries and put the following code to advanced editor in power query

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"

Output

Best Regards!

Yolo Zhu

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