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:

 

AttributeHoursDate
Column614 
Column1010 

Column11

11 

Column13

9 
Column1417 
Column172 
Column194 

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.

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:

     

6 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    1 year ago

    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's avatar
      petermb72
      Helper IV
      1 year ago

      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).

  • 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: