Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional Formula for Power Query

Good Morning Power BI community!   Im hoping to get some help with creating my formula, i have tried but keep getting errors.   This formula works on sharepoint list but does not work in BI:=IF(O...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    Ok. It's because your existing columns are in DateTime format, not date.

    Try this instead:

    let _todayDate = Date.From(DateTime.LocalNow()) in
if [Finding Type] = "Minor"
    and [Containment] = null
    and Duration.TotalDays(_todayDate - Date.From([Created])) > 5
then "Overdue"
else if [Finding Type] = "Major"
    and [Containment] = null
    and Duration.TotalDays(_todayDate - Date.From([Created])) > 1
then "Overdue"
else null

     

    Pete

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
3 years ago

Hi Anonymous ,

 

Your text description translates into Power Query M something like this:

 

let _todayDate = Date.From(DateTime.LocalNow()) in
if [Finding Type] = "Minor"
    and [Containment] = null
    and Duration.Days(_todayDate - [Created]) > 5
then "Overdue"
else if [Finding Type] = "Major"
    and [Containment] = null
    and Duration.Days(_todayDate - [Created]) > 1
then "Overdue"
else null

 

Pete

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Pete, Thank you very much for the response!

Apologies for the delay in response have been out of office. 

I attempted adding in the coulmn and had an error. It looks like its reading the then "Overdue" as a column

Please see snip:

Thanks for the help so far. 

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

     

    Apologies, I forgot to put the outputs into quotations as "Overdue".

    I've corrected my original post.

     

    Pete