Multiple Sumifs

[Anonymous]

I have a big table (8000 rows *123 columns). I need to do sumifs (excel) in multiple columns). I found this post that helps me to group a column, but I am looking for multiple columns.

https://community.powerbi.com/t5/Power-Query/SUMIF-and-Compounding-formula/m-p/754512

Currently I do it on excel with =SUMIFS(Table1[Area_Factor],Table1[province],province,Table1[y_2001],Category) changing the year each time.

This is an example of how the table looks like

id	province	y_2001	y_2002	y_2003	y_2004	Area_Factor
1	a1	forest	forest	forest	forest	30
2	a1	forest	forest	cropland	cropland	25
3	a1	grassland	grassland	grassland	grassland	30
4	a1	forest	cropland	cropland	Settlement	25
5	a1	grassland	Settlement	Settlement	Settlement	25
6	b2	forest	forest	forest	forest	21
7	b2	forest	forest	cropland	cropland	21
8	b2	grassland	grassland	grassland	grassland	26
9	b2	grassland	cropland	cropland	Settlement	26
10	b2	Settlement	Settlement	Settlement	Settlement	21
11	c3	forest	forest	forest	forest	33
12	c3	forest	forest	cropland	cropland	40
13	c3	grassland	grassland	grassland	grassland	40
14	c3	forest	cropland	cropland	Settlement	33
15	c3	Settlement	Settlement	Settlement	Settlement	40

And this is the kind of answer I am looking for, with sums by province/category and by year

Province	Category	2001	2002	2003	2004
a1	forest	80	55	30	30
a1	grassland	55	30	30	30
a1	Settlement	0	25	25	50
a1	cropland	0	25	50	25
b2	forest	42	42	21	21
b2	grassland	52	26	26	26
b2	Settlement	21	21	21	47
b2	cropland	0	26	47	21
c3	forest	106	73	33	33
c3	grassland	40	40	40	40
c3	Settlement	40	40	40	73
c3	cropland	0	33	73	40

I'd appreciate any guidance.

Carlos

[ImkeF · Accepted solution]

Hi @Anonymous ,

this is where Power Query really shines:

1) Delete the id-column

2) Select columns: "province" and "Area-Factor"

3) Transform -> Unpivot -> Unpivot other columns

4) Check columns "Attribute" and Area -> Transform  -> Pivot (keep default aggregation-selection: Sum)

Full code to follow along (paste into advanced editor):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLLDsIgEEX/hXUX5VX1O1ySLrCim9oa4P9jeUwMKZipGzIEztzhBKUIJR3RYXms1jj/u+A9GTtFWJuZ7Pqe9XIvSyYjx4F7Wu1cPsPUOVfscutxV+P9bF5m8d9sWcsuLrY3ucWwlTeGVMVoZE5tpqEqcWfgjqpiQ+QvNR5jK+G0Bx6pqGyS3kCD7olj/xZPEGtD9flF+hyUA3hUGTQQu2SEMBhbAvyPsDDB+AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, province = _t, y_2001 = _t, y_2002 = _t, y_2003 = _t, y_2004 = _t, Area_Factor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"province", type text}, {"y_2001", type text}, {"y_2002", type text}, {"y_2003", type text}, {"y_2004", type text}, {"Area_Factor", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"id"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"province", "Area_Factor"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Area_Factor", List.Sum)
in
    #"Pivoted Column"

[Anonymous]

Great! Thank you.. the answer was exactly what I wanted.

[Jimmy801]

Hello

you have to apply a UnPivotOther to unpivot you years-columns, then rename the year (if you want to delete the y_) and afterwards delete the ID-Column and Pivot it again. Here the code

let
	Source = #table 
	(
		{"id","province","y_2001","y_2002","y_2003","y_2004","Area_Factor"},
		{ {"1","a1","forest","forest","forest","forest","30"}, {"2","a1","forest","forest","cropland","cropland","25"}, {"3","a1","grassland","grassland","grassland","grassland","30"}, {"4","a1","forest","cropland","cropland","Settlement","25"}, {"5","a1","grassland","Settlement","Settlement","Settlement","25"}, {"6","b2","forest","forest","forest","forest","21"}, {"7","b2","forest","forest","cropland","cropland","21"}, {"8","b2","grassland","grassland","grassland","grassland","26"}, {"9","b2","grassland","cropland","cropland","Settlement","26"}, {"10","b2","Settlement","Settlement","Settlement","Settlement","21"}, {"11","c3","forest","forest","forest","forest","33"}, {"12","c3","forest","forest","cropland","cropland","40"}, {"13","c3","grassland","grassland","grassland","grassland","40"}, {"14","c3","forest","cropland","cropland","Settlement","33"}, {"15","c3","Settlement","Settlement","Settlement","Settlement","40"} } 
	),
	ChangedType = Table.TransformColumnTypes
	(
		Source,
		{{"Area_Factor", type number}}
	),
	UnpivotOther = Table.UnpivotOtherColumns
	(
		ChangedType,
		{"id", "province", "Area_Factor"},
		"Attribute",
		"Value"
	),
	DeleteID = Table.RemoveColumns
	(
		UnpivotOther,
		{"id"}
	),
	ReplaceY_ = Table.ReplaceValue
	(
		DeleteID,
		"y_",
		"",
		Replacer.ReplaceText,
		{"Attribute"}
	),
	PivotYear = Table.Pivot
	(
		ReplaceY_,
		List.Distinct
		(
			ReplaceY_[Attribute]
		),
		"Attribute",
		"Area_Factor",
		each if List.IsEmpty
		(
			_
		)
	then 0 else List.Sum
	(
		_
	)
)
 in 
	PivotYear

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy