Filter by letter and sum by year

[OscarSuarez10]

Hello, I´m trying to sum the production of some products by year but I can´t sum all the products, I only want to sum some specific names, for example in the following table I only want the production of the products that have the word cheme in their name, as you can see the sum for the first year is 153 and for the sencond year is 1099, How can I do this using power query?

Year	product	production
1	arbol1	10	cheme year1	153
1	arbol2	20
1	arbol3	5
1	arbol4	4
1	cheme1	78
1	cheme2	52
1	cheme3	13
1	cheme4	10
1	arbol5	9
1	arbol6	4
1	arbol7	1
2	arbol1	78
2	arbol2	45
2	arbol3	98
2	arbol4	12
2	cheme1	987	cheme year2	1099
2	cheme2	12
2	cheme3	45
2	cheme4	55
2	arbol5	3
2	arbol6	11
2	arbol7	22

[Nolock · Accepted solution]

Hi @OscarSuarez10,

I've prepared a solution for you. All that you can just click together in the PowerQuery Editor UI. Go through all the steps and if you don't understand one of them, don't hesitate to ask.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/BCsMgEET/xXMOdXWjfkvIoS1CDgmF/v+hO1LEkdyGh+7M2zbn3eKe39fnRPAPty8DEwsysWBBGUULsaP3Ua+KkDIzXFNhhms+MIs3S9RCYbRSa0MJPxuSUes/REatqMwwpEzv2hDprIuVnBjKzcPAJd1Mp2KYBUYw85MH1MQq9h8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ear = _t, product = _t, production = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ear", Int64.Type}, {"product", type text}, {"production", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([product], "cheme")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ear"}, {{"Cheme Production", each List.Sum([production]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Cheme Year", each "cheme year" & Text.From([ear])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ear"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Cheme Year", "Cheme Production"})
in
    #"Reordered Columns"