Forum Discussion

gavin007's avatar
gavin007
Icon for Helper V rankHelper V
6 years ago
Solved

invalid identifier for list.sum syntax

Hi, I use group by window to sum Rev Act and GM Act by InvoiceDate and OrderNo_Revised_Item. it all works well until I try to amend the sum of Rev Act by applying a condition in when evaluating the s...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    Hi gavin007 ,

    yes, I didn't pay attention to the other code. Problem is, that you have used the "each"-keyword twice and now the syntax sugar doesn't know what to refer to.

    Therefore I'd recommend to transform at least one of the functions to the explicit syntax:

     

    Table.Group(Source, {"InvoiceDate", "OrderNo_Revised_Item"}, {{"Ext Rev Act", each List.Sum(Table.SelectRows(Source, (row) => row[#"I/C"]="C") [Rev Act]), type nullable number}, {"GM Act", each List.Sum([GM Act]), type nullable number}})

     

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

Hi gavin007 

that's probably due to the special characters in the field name. Try to escape it like so: [#"C/O"].

 

  • gavin007's avatar
    gavin007
    Icon for Helper V rankHelper V
    6 years ago

    ImkeF It partialy work but it then it has error message like "We cannot convert the value 380000 to type Function."

    Is there something wrong with the type?

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hi gavin007 ,

      yes, I didn't pay attention to the other code. Problem is, that you have used the "each"-keyword twice and now the syntax sugar doesn't know what to refer to.

      Therefore I'd recommend to transform at least one of the functions to the explicit syntax:

       

      Table.Group(Source, {"InvoiceDate", "OrderNo_Revised_Item"}, {{"Ext Rev Act", each List.Sum(Table.SelectRows(Source, (row) => row[#"I/C"]="C") [Rev Act]), type nullable number}, {"GM Act", each List.Sum([GM Act]), type nullable number}})

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        hi gavin007 

         

         

        I wonder if you wanted to add [Rev Act] for the entire Source table or just for each sub-group.
        In the latter case, you should replace Source with _.

        then from

         Table.SelectRows(Source, each [I/C]="C") [Rev Act])

        to

        Table.SelectRows(_, each [I/C]="C") [Rev Act])

         

         

        In general, for faster and more useful help you should provide the expected and the input data and if any error appears in your code, the complete copy of the code used.