Conditional AggregateTableColumn with List.Sum and Table.SelectRows

[Spigaw]

Hello everyone,

I have a query called "HC_Fi" in which I have:

• Periods;
• Cost centers;
• Collaboration type;
• Value between 0 and 1 for every line called BSC;
• Value between 0 and 1 for every line called Registered (calculated).

In a second query, I need to:

• Aggregate and delete duplicate rows based on Periods and Costs centers;
• For every line, get a sum of BSC only if Collaboration type = "INTERIMAIRE";
• For every line, get a sum of Registered (calculated).

I tried the following code in M, but Power Query returns an error saying that it can't convert a Table to a List:

= Table.AggregateTableColumn(#"Merged queries", "HC_Fi", {{"Registered (calculated)", List.Sum(Table.SelectRows(HC_Fi, each [Collaboration type] = "INTERIMAIRE")), "Sum of Registered (calculated)"}, {"BSC", List.Sum, "Sum of BSC"}})

Can someone tell me what I did wrong?

Thanks in advance!

[Vera_33]

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!

[Vera_33 · Accepted solution]

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:
  1. 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)
  2. 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!

[Vera_33]

Hi @Spigaw 

Good questions for learning

• Why did you add "_" as the first argument of Table.SelectRows instead of a table?

if you do each _, you get the table of all rows - try it you will see. Read his blog to understand M better

https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressi...

• Why did you not keep "type number" at the end of the expression?

because I was lazy...I typed it so...bad habit, you can add them:)

[Spigaw]

Thank you for the reading material, I'll read it to better understand what I am doing with my data. And there is no problem with being lazy, as long as everything works...

Thank you again for your help, this forum is awesome!