Total production pero job and year

[OscarSuarez10]

Hello 

I have the follwing table in the Query Editor:

JOB	ITEM	YEAR	PRODUCTION
0	A	1	100
0	A	2	300
0	B	1	200
0	B	2	500
1	A	1	450
1	A	2	78
1	B	1	150
1	B	2	32
2	A	1	46
2	A	2	79
2	B	1	42
2	B	2	12

And I need to get the total production pero Jon and Year like this:

JOB	YEAR	PRODUCTION
0	1	300
0	2	800
1	1	600
1	2	110
2	1	88
2	2	91

Can you help me ?

[v-frfei-msft · Accepted solution]

Hi @OscarSuarez10 ,

Here we can group by the JOB and Year column in power query to get the excepted result.

M code for your reference :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcxLDsAgCEXRvTB2AKj9DNttGPe/DcGIhQ4IycnLbQ0QEjxypIcIPX3GctnZu3b8M93VZeR6pUbT3Xltshy5meUyT2JfOwLN2L3JYoUD6SehPgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [JOB = _t, ITEM = _t, YEAR = _t, PRODUCTION = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOB", Int64.Type}, {"ITEM", type text}, {"YEAR", Int64.Type}, {"PRODUCTION", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JOB", "YEAR"}, {{"Sum", each List.Sum([PRODUCTION]), type number}})
in
    #"Grouped Rows"

[OscarSuarez10]

Thank you, If I have another production column (PRODUCTION2), how can I add it to do the same thing? I am trying to do this: #"Grouped Rows" = Table.Group(#"Changed Type", {"JOB", "Year"}, {{"Sum", each List.Sum([PRODUCTION),type number}}, {{"Sum2", each List.Sum([PRODUCTION2]),type number}}) But it didn´t worked