Conditional Formula for Power Query

[Anonymous]

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!

[BA_Pete]

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]

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]

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

[Anonymous]

Didn't know you could do that! Thanks that a top tip!

Please see error message:

Thanks for the quick response.

[BA_Pete · Accepted solution]

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

[Anonymous]

Hi Pete, Thank you so much for your help, that did the trick! 

I have learned some new tricks now.

Thank you once again! 

[BA_Pete]

No problem, happy to help 🙂

Don't forget to give a thumbs-up on any posts that have helped you 👍

Pete

[Anonymous]

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]

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

I've corrected my original post.

Pete