Forum Discussion

MayAnk__Rathi's avatar
MayAnk__Rathi
Helper I
1 year ago
Solved

Issue in applying If & Or function

I face issue in implementing a formula in power query

Formula is: If date in Column A is equal to today's date or yesterday's date or tomorrow's date then result is T else F.

Please guide me.

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    MayAnk__Rathi Open your Power Query editor.
    Add a new custom column.
    Use the following formula in the custom column:

    let
    Today = Date.From(DateTime.LocalNow()),
    Yesterday = Date.AddDays(Today, -1),
    Tomorrow = Date.AddDays(Today, 1)
    in
    if [ColumnA] = Today or [ColumnA] = Yesterday or [ColumnA] = Tomorrow then "T" else "F"

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    MayAnk__Rathi Open your Power Query editor.
    Add a new custom column.
    Use the following formula in the custom column:

    let
    Today = Date.From(DateTime.LocalNow()),
    Yesterday = Date.AddDays(Today, -1),
    Tomorrow = Date.AddDays(Today, 1)
    in
    if [ColumnA] = Today or [ColumnA] = Yesterday or [ColumnA] = Tomorrow then "T" else "F"

  • abdelazizallam0's avatar
    abdelazizallam0
    New Member
    1 year ago

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1_2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    today = Date.From(DateTime.LocalNow()),
    yesterday = Date.AddDays(today, -1),
    tomorrow = Date.AddDays(today, 1)
    in
    if [date] = today or [date] = yesterday or [date] = tomorrow then "T" else "F")
    in
    #"Added Custom"

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Hi MayAnk__Rathi 

     

    if Number.Abs(Number.From(Date.From(DateTime.LocalNow())-[ColumnA]))<=1 then "T" else "F"

    Stéphane 

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year ago

    Hi MayAnk__Rathi 

     

    use the following formula

    let
    today = Date.From(DateTime.LocalNow()),
    value = [Column A],
    result = if value = today or value = Date.AddDays(today, -1) or value = Date.AddDays(today, 1) then "T" else "F"
in
    result
  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi MayAnk__Rathi,

     

    Thank you bhanu_gautam for the response to the query.

     

    I'm glad you found a solution and resloved  the query. Kindly mark the useful reply as the accepted solution so that others  in the community can find it quickly.

     

    Thankyou for connecting with Microsoft Community Forum.