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(OR(AND([Finding Type]="Minor",ISBLANK(Containment),(TODAY()-Created)>5),AND([Finding Type]="Major",ISBLANK(Containment),(TODAY()-Created)>1)),"Overdue","")

 

It reads: If "Finding Type" is "Minor", "containment" is "blank" and "Created" is greater then 5 days then show "Overdue" 

Next condition is If "Finding Type" is "Major", "containment" is "blank" and "Created" is greater then 1 days then show "Overdue" 

 

Please help a newbie out!

 

Many thanks for reading!

 

 

 

 

 

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

9 Replies

  • BA_Pete's avatar
    BA_Pete
    Super 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
        Super User
        3 years ago

         

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

        I've corrected my original post.

         

        Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Good Morning,

      I have inserted the updated calculation and now gettings errors, i have validated the errors and the condition is true on errors but not sure why its showing error instead of "Overdue". Where the condtion is not true its showing "null" as per condition. Thoughts as to why this can be?

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Can you click in one of the error cells (not the word 'error' itself, just the space in the cell next to it) and let me know what the error message says please?

         

        Pete