Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help Please: If and then with Power Query specific to date range

Hello!

I'd like to create a custom column in a Power Query that takes my Earning Date, and if it falls within a certain date range, then the custom column will show the earning period. I believe the formula would look something like this (although it isn't working):

IF([earningDate] >= #date(2020,7,1) & [earningDate] <= #date(2020,12,31) then "FY21H1"))

 

Thank you for your help!

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Anonymous , the syntax for if statement in M code is like (case sensitive)

    if ... then ... else ...

    as to your issue, you might want to try

    if [earningDate] >= #date(2020,7,1) and [earningDate] <= #date(2020,12,31) then "FY21H1" else "out of range"

4 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous , the syntax for if statement in M code is like (case sensitive)

    if ... then ... else ...

    as to your issue, you might want to try

    if [earningDate] >= #date(2020,7,1) and [earningDate] <= #date(2020,12,31) then "FY21H1" else "out of range"
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you CNENFRNL. If I need to add another IF, would it look like this:

      if [earningDate] >= #date(2020,7,1) and [earningDate] <= #date(2020,12,31) then "FY21H1" else "out of range"
      if [earningDate] >= #date(2020,1,1) and [earningDate] <= #date(2020,6,30) then "FY20H1" else "out of range"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Apologies as this is my first time using this community support. CNENFRNL Thank you. If I need to add another IF, how would I apply the 2nd IF statement, and possible 3 and 4 other IF statements. Thank you in advance!