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

  • 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
      Icon for Super User rankSuper 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

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

        Please see error message:

        Thanks for the quick response.