Error Expression.Error: We cannot apply operator - to types Function and Function.

[beatrizalbuqu]

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 🙂

[Anonymous]

But wait where is the rest of the query?

[Anonymous]

Try it without the "each"

--Nate

[beatrizalbuqu]

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?"

[m_dekorte]

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

[beatrizalbuqu]

Hi m_dekorte,

Thanks for your reply!

The [FY START] and the [FY END] are columns from the Costum1 table. Sorry for not mentioning it earlier!

I've tried what you suggested but another error comes up: "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

So I've tried creating a new variable called duration and it worked (as below). I know it's not the best practice but that's what I could do. If there's any other tip I'd be more than grateful.

startdate = #date(2022,11,01),
enddate = #date(2022,11,30),

inicial = each if [FY START] <= startdate then startdate else [FY START],
final = each if [FY END DATE] <= enddate  then [FY END DATE] else enddate,

duration =
each if [FY START] <= startdate and [FY END DATE] <= enddate then Duration.Days([FY END DATE]-startdate)+1
else if [FY START] <= startdate and [FY END DATE] > enddate then Duration.Days(enddate-startdate)+1
else if [FY START] > startdate and [FY END DATE] <= enddate then Duration.Days([FY END DATE]-[FY START])+1
else if [FY START] > startdate and [FY END DATE] > enddate then Duration.Days(enddate-[FY START])+1 else "0"

 in Table.AddColumn(Custom1, "November hours", duration, type number)

[m_dekorte]

Hi @beatrizalbuqu,

Do you mean by "columns" that this is returning a list?

The [FY START] and the [FY END] are columns from the Costum1 table. 

[beatrizalbuqu]

They're fields from Custom1. Sorry for not using the correct names for things.
When you look at the table visualisation, these are columns headers names and that's why I called them columns.

[m_dekorte · Accepted solution]

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
    final

I hope this is helpful

[beatrizalbuqu]

Hi @m_dekorte,

I get what you mean! Thank you, it worked!!