Forum Discussion
Helper IIIConditional AggregateTableColumn with List.Sum and Table.SelectRows
Hi Spigaw
I have this way, you copy the M code to paste in Advanced Editor to see there is one step after Change Type
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7Zg9DoMwDIXvwpzBgfKTMUKoitR2ADbE/a/RiooyxTbChCjqAFk+2c/PJkSZpiyHXEOdqaxt9ee9Pu41dr17Wtd32axOxmAfNoz27qiUg33Y3nkRYCCqKGmKkSstRKoLkt08huShEVCNHyroOMERlmA56Lhocrjis5AtOSafJecnoJo6yaqOqbmJIqga2VQEQn5XQdWkKDguZ0AZsy2eaOVCgN4WD1jRylYEHfkdcWJBSLdjExwLUvMRUDcOhExyzenUL5KI6PQQ0mF2HGQLYMb5nk5AlZWfaxZiOTWqnDFBVEru/Ei5jZQWrPdGFEE7bzgeb5GQ0yk7nZTsoCb+BSdUeEwI8t/TQIcRZ9BmXqQIu+uUzEZuhMHrD68ovv4fZfQFDO4i4z72XGZ+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, CC = _t, BSC = _t, #"Registered (calculated)" = _t, #"Collaboration type" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"CC", type text}, {"BSC", Int64.Type}, {"Registered (calculated)", Int64.Type}, {"Collaboration type", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Period", "CC"}, {{"test", each List.Sum(Table.SelectRows(_, each[Collaboration type] = "INTERIMAIRE")[#"Registered (calculated)"])}, {"sum of BSC",each List.Sum([BSC])}}) in #"Grouped Rows"
Hi Spigaw
It is my understanding, when you are doing Aggregation on "Registerd (calculated)", it is the the list of this column, so Table.SelectRows in your code is not working. Your text was intended to do sum of BSC when type = "INTERIMAIRE", but your code is doing it for another column. I think it is better to add column to aggregate BSC and Registered seperately, less confused (you have to add two columns, one for each), but still, you can try below one I tried to modify yours
= Table.AggregateTableColumn(#"Merged queries", "HC_Fi",
{
{"Registered (calculated)", each List.Sum(Table.SelectRows(#"Merged queries"[HC_Fi]{0}, each [Collaboration type] = "INTERIMAIRE")[#"Registered (calculated)"]), "Sum of Registered (calculated)"},
{"BSC", List.Sum, "Sum of BSC"}
})
- Spigaw
Thank you for your help!
I tried your formula, however I don't find the expected results; it returns null for each line on the Registered (calculated). Oddly enough, when changing from "INTERIMAIRE" to another value available in the column, the formula returns the same result for each line.
To make it simpler, I uploaded a file you can find here. To summarize:
- Database = blue table
- Expected results = orange table
- Results from the query = green table
Let me know if you need any details to solve this problem. I feel disappointed that I can attain the good results with a simple SUMPRODUCT and can't do the same with Power Query...
Thanks again for your time and help!
Hi Spigaw
I have this way, you copy the M code to paste in Advanced Editor to see there is one step after Change Type
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7Zg9DoMwDIXvwpzBgfKTMUKoitR2ADbE/a/RiooyxTbChCjqAFk+2c/PJkSZpiyHXEOdqaxt9ee9Pu41dr17Wtd32axOxmAfNoz27qiUg33Y3nkRYCCqKGmKkSstRKoLkt08huShEVCNHyroOMERlmA56Lhocrjis5AtOSafJecnoJo6yaqOqbmJIqga2VQEQn5XQdWkKDguZ0AZsy2eaOVCgN4WD1jRylYEHfkdcWJBSLdjExwLUvMRUDcOhExyzenUL5KI6PQQ0mF2HGQLYMb5nk5AlZWfaxZiOTWqnDFBVEru/Ei5jZQWrPdGFEE7bzgeb5GQ0yk7nZTsoCb+BSdUeEwI8t/TQIcRZ9BmXqQIu+uUzEZuhMHrD68ovv4fZfQFDO4i4z72XGZ+Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, CC = _t, BSC = _t, #"Registered (calculated)" = _t, #"Collaboration type" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"CC", type text}, {"BSC", Int64.Type}, {"Registered (calculated)", Int64.Type}, {"Collaboration type", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Period", "CC"}, {{"test", each List.Sum(Table.SelectRows(_, each[Collaboration type] = "INTERIMAIRE")[#"Registered (calculated)"])}, {"sum of BSC",each List.Sum([BSC])}}) in #"Grouped Rows"- Spigaw
It works exactly as intended, thank you so much!
So in order to summarize:
- There is no need to duplicate the request and to merge it with the original request
- Two steps have to be applied:
- Grouping the rows based on the cost center and creating a sum of the values (it's possible to do both with the same advanced action with the grouping utility, see screenshot below)
- Adding a Table.SelectRows inside the List.Sum :
Table.SelectRows(_, each[Collaboration type] = "INTERIMAIRE")Advanced grouping
I have two questions regarding the bit of code you added:
- Why did you add "_" as the first argument of Table.SelectRows instead of a table?
- Why did you not keep "type number" at the end of the expression?
Thank you again for all this precious help!
