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 ,

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!