Forum Discussion

eagertolearn1's avatar
eagertolearn1
New Member
1 year ago
Solved

POWER QUERY custom Colum if between 2 dates answer is what ever i type in as example

Can anyone help why this keeps coming up with token litral expected error

 

copy and pasted

if[Posting Date]>=#date(2022,4,1)and<=#date(2023,3,1)then"FY22/23"else if[Posting Date]>=#date(2023,4,1)and<=#date(2024,3,1)then"FY23/24"else

 

etc etc for each financial year i add

 

 

 

  • You are missing the literal after each "and". The compiler doesn't know what that second clause in the "and" should be compared with.  In addition, your algorithm doesn't account for any dates that are in the month of March other than March 1.

    if[Posting Date]>=#date(2022,4,1)and [Posting Date] <=#date(2023,3,1)then"FY22/23"else if[Posting Date]>=#date(2023,4,1)and [Posting Date] <=#date(2024,3,1)then"FY23/24" else

    . 

    By the way, instead of a long "if" statement referring to each fiscal year, you could just compute it from the Posting Date.

     

    From the Advanced Editor:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each 
                                [a=Number.Mod(Date.Year([Posting Date]),100),
                                 b=Date.Month([Posting Date]),
                                 c=if b < 4 
                                    then "FY" & Number.ToText(a-1,"00/") &  Number.ToText(a)
                                    else "FY" & Number.ToText(a,"00/") & Number.ToText(a+1,"00")
                                ][c],type text)

1 Reply

  • You are missing the literal after each "and". The compiler doesn't know what that second clause in the "and" should be compared with.  In addition, your algorithm doesn't account for any dates that are in the month of March other than March 1.

    if[Posting Date]>=#date(2022,4,1)and [Posting Date] <=#date(2023,3,1)then"FY22/23"else if[Posting Date]>=#date(2023,4,1)and [Posting Date] <=#date(2024,3,1)then"FY23/24" else

    . 

    By the way, instead of a long "if" statement referring to each fiscal year, you could just compute it from the Posting Date.

     

    From the Advanced Editor:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Fiscal Year", each 
                                [a=Number.Mod(Date.Year([Posting Date]),100),
                                 b=Date.Month([Posting Date]),
                                 c=if b < 4 
                                    then "FY" & Number.ToText(a-1,"00/") &  Number.ToText(a)
                                    else "FY" & Number.ToText(a,"00/") & Number.ToText(a+1,"00")
                                ][c],type text)