Forum Discussion

beatrizalbuqu's avatar
beatrizalbuqu
Frequent Visitor
3 years ago
Solved

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...
Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Try it without the "each"

 

--Nate

  • beatrizalbuqu's avatar
    beatrizalbuqu
    Frequent Visitor
    3 years ago

    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's avatar
      m_dekorte
      Resident Rockstar
      3 years ago

      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's avatar
        beatrizalbuqu
        Frequent Visitor
        3 years ago

        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)