Forum Discussion

Spigaw's avatar
Spigaw
Helper III
5 years ago
Solved

Conditional AggregateTableColumn with List.Sum and Table.SelectRows

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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Spigaw
      Helper III
      5 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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"