Forum Discussion
Helper IIIFilter by letter and sum by year
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 |
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"
1 Reply
- Nolock
Resident Rockstar
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"