Forum Discussion
Error Expression.Error: We cannot apply operator - to types Function and Function.
Hi, mates!
It's my first message here. I'm still learning the M language so please bear with me. I don't know how to fix the code below.
The main idea is to calculate working days in November/2022 for each person in a table. I have the start working date [FY START] and end working date [FY END] in this table. I've set the november dates as variables and then I've tried to use conditional variables for defining inicial date and final date for the duration function.
= let
startdate = #date(2022,11,01),
enddate = #date(2022,11,30),
employee_start = [FY START],
employee_end = [FY END DATE],
inicial = each if employee_start <= startdate then startdate else employee_start,
final = each if employee_end <= enddate then employee_end else enddate
in Table.AddColumn(Custom1, "November hours", Duration.Days(final - inicial)+ 1, type number)
Please, any help will be amazing!
Thank you 🙂
Thanks for clearing that up beatrizalbuqu
That means you can't refer to [FY START] or any other field (read: column name) outside a table row context. As illustrated below you have to refer to those inside Table.AddColumn's columnGenerator argument.
let startdate = #date(2022,11,01), enddate = #date(2022,11,30), // your missing steps here final = Table.AddColumn( Custom1, "November hours", each Duration.Days( (if [FY START] <= startdate then startdate else [FY START]) - (if [FY END DATE] <= enddate then [FY END DATE] else enddate) ) +1, type number ) in finalI hope this is helpful
9 Replies
Hi Nate!
Sorry I didn't typed the whole query here but there are confidential info there and I can't share it completely.Thank you for your suggestion. I've tried removing the "each" but another error came up:
"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"Hi beatrizalbuqu,
I want to call something out, this: [FY START] syntax is mostly seen when applying 'field access' in those cases it has to be used in a table row context. Right now, looking at the code above it looks as though that context is missing... Try this instead.
let startdate = #date(2022,11,01), enddate = #date(2022,11,30), // your missing steps here final = Table.AddColumn( Custom1, "November hours", each Duration.Days( (if [FY START] <= startdate then startdate else [FY START]) - (if [FY END DATE] <= enddate then [FY END DATE] else enddate) ) +1, type number ) in final
