Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Power Query Nested IF AND Statement

Hi All,   I am working with the fiscal calendar below: January: 01/01/2018 - 01/26/2016 February: 01/27/2018 - 02/23/2018   I would like to create a new column in Power Query that identifies th...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Please refer to the snapshot below and try the formula. Your sample is too short. So I just hardcode these rules.

    if [Requested Date] >= #date(2018, 1, 1) and [Requested Date] <= #date(2018, 1, 26) 
    then #date(2018, 1, 1) 
    else if [Requested Date] >= #date(2018, 1, 27) and [Requested Date] <= #date(2018, 2, 23) 
    then #date(2018, 2, 1) 
    else #date(9999, 12, 31)

    Power-Query-Nested-IF-AND-Statement

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous,

 

Please refer to the snapshot below and try the formula. Your sample is too short. So I just hardcode these rules.

if [Requested Date] >= #date(2018, 1, 1) and [Requested Date] <= #date(2018, 1, 26) 
then #date(2018, 1, 1) 
else if [Requested Date] >= #date(2018, 1, 27) and [Requested Date] <= #date(2018, 2, 23) 
then #date(2018, 2, 1) 
else #date(9999, 12, 31)

Power-Query-Nested-IF-AND-Statement

Best Regards,
Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    This works for what I need it for!

     

    Turns out there is a difference between a date column and a dattime column so I had to adjust your solution slightly to reflect this. I can see how updating this once a new fiscal calendar comes out may be a pain though.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    IF Primary Discipline is blank Get value from Discipline & even if Discipline is blank Get value from Attribute 4 Column and Add new Column “WIR Discipline”