Forum Discussion
We cannot apply field access to the type Number
Hi,
I do not have much experience on this and I am trying to create a function to summary value from another table based on a set of conditions. I tried to use Table. Selectrows but it takes a very long time to generate the results with just 5K records. I am trying to use the Table.SelectRows inside List.Generate.
This is the function created to summary the value:
(Field, tbl as table) =>
let
Deduction =
List.Generate
(
() => [counter = 0],
each [counter]< Table.RowCount(tbl),
each
[counter = [counter]+1],
List.Sum(
Table.SelectRows
(
tbl,
(DeductionTable) => DeductionTable[Search] = Field
)[Deduction Amount])[Deduction Amount],
each [Deduction Amount]
)
in
Deduction
I am search a unique Field from the tbl table and I am matching this field with a field from the table that I want to create the new column.
The table.SelectRows is selecting the correct information for each cell but it has an error which I can not figure.
the error is the following:
An error occurred in the ‘’ query. Expression.Error: We cannot apply field access to the type Number.
Details:
Value=8.24
Key=Deduction Amount
the value (8.24) on this each cell is correct but how can I clear this error. Any idea will be greatly appretiated.
(the tbl table
9 Replies
Hi vsolano
Looks like you are referencing the [Deduction Amount] field twice, towards the end.
)[Deduction Amount])[Deduction Amount],Instead try this
(Field, tbl as table) as list => List.Generate( () => [counter = 0, Deduction = 0 ], each [counter] < Table.RowCount(tbl), each [counter = [counter] + 1, Deduction = List.Sum( Table.SelectRows(tbl, (DeductionTable) => DeductionTable[Search] = Field)[Deduction Amount] )], each [Deduction] )I hope this is helpful
that for the response.
This almost fix the issue.
Now the formula return a list with the sum of all values, but the sum amount is repeated as many times as the value from the tbl table. In other words if the tbl table has six cells which I want to sum, right not it return the sum value six times
Hi vsolano,
Clear, let's switch to List.Transform
Give this a go, it will return an aggregate table with one result per 'field'
(Fields as list, tbl as table) as table => Table.FromRecords( List.Transform( List.Distinct( Fields ), (x)=> [ Field = x, Deduction = try List.Sum( Table.SelectRows( tbl, (t) => t[Search] = x )[Deduction Amount] ) otherwise null ] ), type table [Field = text, Deduction = nullable number] )Hope I didn't make any typo's 😉
Let me know if this works for you.
(Field, tbl as table)as list => let Deduction = List.Generate ( () => [counter = 0, Deduction = 0], each [counter]< Table.RowCount(tbl), each [counter = [counter]+1, Deduction = List.Sum( Table.SelectRows ( tbl, (DeductionTable) => DeductionTable[Search] = Field)[Deduction Amount] )], each [Deduction] ) in Deduction
this is the result list
vsolano wrote:The list coming from the column Fields is unique. The one that I tried to summary is the one from the tbl table and add the sum value on another query that has the fields column.
First. Here's another approach to consider.
If you want to summarize the tbl table, you can use Group By, set Field as key and add an aggregate column for [Deduction Amount] next you could use this summary in a Merge operation with your other query.
Second. If you're looking for a custom function, this should do it.
(Field, tbl as table) as nullable number => try List.Sum( Table.SelectRows(tbl, (DeductionTable) => DeductionTable[Search] = Field)[Deduction Amount] ) otherwise nullAmend to your needs.
I hope this is helpful
I was trying to look for another option that can perform better. I tried the merge option but on the search field I need return records from a range of dates
