Sum values in child table based on two filters

[SteveDesmedt]

Hi all, 

I'm pretty new in Power Query and would like to achive the following :

I have two tables :

Table 1

Entry No.

1

2

3

4

Table 2

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

I would like to add three additional columns in table 1 per cost code (manually)

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)

I don't know how to achive this in Power query.  I'm not able two have second table filtered on entry No. and fixed filter value (AA, BB, CC) for each different column. 

Thanks in advance.

Steve

[v-xinruzhu-msft · Accepted solution]

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.

[AlienSx]

hello, @SteveDesmedt in PQ Editor select Cost Code column, go to Transform tab, find Pivot Column button,  use Cost Amount as values column and choose Sum as aggregation function.