Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Great! Thank you.. the answer was exactly what I wanted.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.