Forum Discussion

petermb72's avatar
petermb72
Helper IV
1 year ago
Solved

Power Query Editor Nested if statements to put dates in a column

I am trying to put dates in a column based on the contents of another column.  Here is an example of the data:   Attribute Hours Date Column6 14   Column10 10   Column11 11   ...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi petermb72 ,

    Sorry about that, i've made a mistake in my Code, but please, use the bellow M code:

    if [Attribute] = "Column19" then Date.From(DateTime.LocalNow())  // Today's date
else if [Attribute] = "Column17" then Date.AddDays(Date.From(DateTime.LocalNow()), -1)  // Yesterday's date
else if [Attribute] = "Column14" then Date.AddDays(Date.From(DateTime.LocalNow()), -2)  // Day before yesterday
else if [Attribute] = "Column13" then Date.AddDays(Date.From(DateTime.LocalNow()), -3)
else if [Attribute] = "Column11" then Date.AddDays(Date.From(DateTime.LocalNow()), -4)
else if [Attribute] = "Column10" then Date.AddDays(Date.From(DateTime.LocalNow()), -5)
else if [Attribute] = "Column6" then Date.AddDays(Date.From(DateTime.LocalNow()), -6)
else null

     

    Your output will look like this:

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

Hi petermb72 ,

Please try this updated M code:

if [Attribute] = "Column19" then Date.From(DateTime.LocalNow())  // Today's date
else if [Attribute] = "Column17" then Date.From(DateTime.AddDays(DateTime.LocalNow(), -1))  // Yesterday's date
else if [Attribute] = "Column14" then Date.From(DateTime.AddDays(DateTime.LocalNow(), -2))  // Day before yesterday
else if [Attribute] = "Column13" then Date.From(DateTime.AddDays(DateTime.LocalNow(), -3))
else if [Attribute] = "Column11" then Date.From(DateTime.AddDays(DateTime.LocalNow(), -4))
else if [Attribute] = "Column10" then Date.From(DateTime.AddDays(DateTime.LocalNow(), -5))
else if [Attribute] = "Column6" then Date.From(DateTime.AddDays(DateTime.LocalNow(), -6))
else null
petermb72's avatar
petermb72
Helper IV
1 year ago

I am getting the following error when I put your formula in:

 

Expression.Error: THe name 'DateTime.AddDays' wasn't recognized. Make sure it's spelled correctly.

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi petermb72 ,

    Sorry about that, i've made a mistake in my Code, but please, use the bellow M code:

    if [Attribute] = "Column19" then Date.From(DateTime.LocalNow())  // Today's date
else if [Attribute] = "Column17" then Date.AddDays(Date.From(DateTime.LocalNow()), -1)  // Yesterday's date
else if [Attribute] = "Column14" then Date.AddDays(Date.From(DateTime.LocalNow()), -2)  // Day before yesterday
else if [Attribute] = "Column13" then Date.AddDays(Date.From(DateTime.LocalNow()), -3)
else if [Attribute] = "Column11" then Date.AddDays(Date.From(DateTime.LocalNow()), -4)
else if [Attribute] = "Column10" then Date.AddDays(Date.From(DateTime.LocalNow()), -5)
else if [Attribute] = "Column6" then Date.AddDays(Date.From(DateTime.LocalNow()), -6)
else null

     

    Your output will look like this:

     

    • petermb72's avatar
      petermb72
      Helper IV
      1 year ago

      Awesome!  That was the trick! Thank You so much!