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

[petermb72]

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
Column13	9
Column14	17
Column17	2
Column19	4

What I want is to have todays date put in the date column for Column19, yesterdays date put in the column for column17, daybefore date put in column14.

I thought this would work for this proceess but I am getting an error after the first date gets inserted:

if [Attribute] = "Column19" then DateTime.LocalNow()
else if [Attribute] = "Column17" then DateTime.AddDays(DateTime.LocalNow(), -1)
else if [Attribute] = "Column14" then DateTime.AddDays(DateTime.LocalNow(), -2)
else if [Attribute] = "Column13" then DateTime.AddDays(DateTime.LocalNow(), -3)
else if [Attribute] = "Column11" then DateTime.AddDays(DateTime.LocalNow(), -4)
else if [Attribute] = "Column10" then DateTime.AddDays(DateTime.LocalNow(), -5)
else if [Attribute] = "Column6" then DateTime.AddDays(DateTime.LocalNow(), -6)
else null

I get an error that DateTime.AddDays wasn't recognized.  I am also not sure if this is the right formula to use.  Any help would be appreciated.

[Bibiano_Geraldo]

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]

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 · Accepted solution]

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]

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

[VijayP]

@petermb72 

It is most important to know why you wanted to do instead of what you wanted to do , it helps us to give right answer

[petermb72]

I want to have the date for each of the items in the table going back for the week.  I bring in the data everyday and it is a rolling 7 day period so the date would change for each line every day (forward one day).