invalid identifier for list.sum syntax

[gavin007]

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 source, which is [I/C]="C". (highlight in bold), I got an error message like invalid identifier.

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

I couldn't figure out what is wrong with that. Can anyone help?

[Greg_Deckler]

@ImkeF , @edhans 

[ImkeF]

Hi @gavin007 

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

[gavin007]

@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 · Accepted solution]

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}})

[gavin007]

@ImkeF That is awsome. It is working perfecly. Can i humbly ask how and where can i learn this syntax? I am good with click and drop but when it comes to scripting,  expecially with the M language structue, i find it difficult to write a script like that.

[ImkeF]

Hi @gavin007 ,

glad to hear that it worked for you.

I've collected some learning resources here: https://www.thebiccountant.com/learning-resources/ 

[Anonymous]

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.

[gavin007]

@Anonymous that actual help. Many Thanks!