Conditional sum in nested table

[AriB]

Hey you guys,

I'm awfully new to Power Query and M code, and need help with a problem that I cannot seem to tackle however many solved forum threads and tutorials I may browse...

I have a Grouped table identifying people with a unique ID, and in the "Agent" column, I have nested tables with a list of work contracts for each of these people (Place of work called "Nom Ets", Contract Start Date called "Déb. Aff.", Contract End Date called "Fin Aff.", Hours Worked called "Qté Occ.", etc.)

I have managed, so far, to identify whether each person is currently employed (using Start and End Dates: some contracts are expired, some have been entered in advance) so as to, in each nested table, into a custom column called "Affecté", put 1 if employed, or 0 if not.

What I now need to do is, for each person (and therefore each nested table) to sum the Hours Worked (the numbers contained in the "Qté Occ." column) for all of their contracts ONLY IF that contract means current employment (i.e., if "Affecté" is set to 1). I want to place this result in an aggregated column called "Qtité Occ." (which is very close in name to the nested column I'm summing, I'll admit). As I am new to M, the syntax is proving (extremely) tricky, and as the error messages are not (at all) explicit, I'm stuck...

What I'm doing now (which fails) is this:

//Duplicating the "Agent" column and calling it "Qtité Occ."
#"Création colonne quotité" = Table.DuplicateColumn(#"Calcul du nombre d'affectations", "Agent", "Qtité Occ."), 
//(Trying to) conditional sum the nested table column "Qté Occ." if "Affecté" is set to 1, and assign the result to the "Qtité Occ." column
#"Calcul de la quotité" = Table.AggregateTableColumn(#"Création colonne quotité", "Qtité Occ.", {{"Qté Occ.", each List.Sum(Table.SelectRows(_, each ([Affecté] = 1))), "Qté Occ."}}),

for which I get an error message saying Power Query wasn't able to convert a list into a table (or something of the kind...)

Is there any way I could modify the second line (the conditional sum) for it to work?

Thanks in advance to anyone who might feel inclined to give me a hand: any help will be very gladly appreciated!

Best,

Ari  ;o)

[OwenAuger]

@AriB Glad it worked 🙂

Yes, in fact Table.TransformColumns can transform multiple columns in one step.

I used a short-hand syntax since we were transforming just one column.

In general , you can provide a list of transformations, where each transformation is specificed by a list of the form:

{ "Column Name", TransformationFunction, type }

There are some other optional arguments as well.

https://learn.microsoft.com/en-us/powerquery-m/table-transformcolumns

Regards

[OwenAuger · Accepted solution]

Hi @AriB 

Table.AggregateTableColumn acts on only a single column within the nested table, so it can't apply a filter to any columns other than the one being aggregated.

Instead, try this, which filters the nested table with Table.TransformColumns before applying Table.AggregateTableColumn.

  #"Création colonne quotité" = Table.DuplicateColumn(
    #"Calcul du nombre d'affectations", 
    "Agent", 
    "Qtité Occ."
  ), 
  #"Calcul de la quotité" = Table.AggregateTableColumn(
    Table.TransformColumns(
      #"Création colonne quotité",
      {"Qtité Occ.", each Table.SelectRows(_, each [Affecté] = 1)}
    ), 
    "Qtité Occ.", 
    {{"Qté Occ.", List.Sum, "Qté Occ."}}
  )

You could also write it this way with a separate step "Filter nested table" applying Table.TransformColumns:

  #"Création colonne quotité" = Table.DuplicateColumn(
    #"Calcul du nombre d'affectations", 
    "Agent", 
    "Qtité Occ."
  ), 
  #"Filter nested table" = Table.TransformColumns(
    #"Création colonne quotité",
    {"Qtité Occ.", each Table.SelectRows(_, each [Affecté] = 1)}
  ),
  #"Calcul de la quotité" = Table.AggregateTableColumn(
    #"Filter nested table", 
    "Qtité Occ.", 
    {{"Qté Occ.", List.Sum, "Qté Occ."}}
  )

Do these work for you?

Regards

[AriB]

Thank you, @OwenAuger, works GREAT! I'm going to try and re-use Table.TransformColumns in other contexts as well. So if I understand you correctly, Table.TransformColumns applies any kind of function to any designated table column ? If so, very interesting!