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 li...
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"

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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-expressions-let

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

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!