none
IF function in PowerQuery RRS feed

  • Question

  • Hi

    I have a table with 3 columns and I am trying to create another but I am having a bit of difficulty getting the right query

    Date Actions Due
    1/1/2018
    1/2/2018
    1/2/2018
    1/4/2018

    Date Closed
    null (Action still open)
    1/1/2018
    1/4/2018
    1/4/2018

    Days Early/Late
    null
    1
    -2
    0

    Late Actions Count (NEW COLUMN)
    1
    0
    1
    0

    [Days Early/Late]=[Date Actions Due]-[Date Closed] --If Date Closed = null, [Days Early/Late] will be null.

    The query I am trying to use for [Late Actions Count] is as follows but its giving an error 'Token Eof expected' on THEN

    NEW COLUMN LOGIC: [Late Actions Count] = IF([Date Action Due] < Now() and [Date Closed] = "null") or [#"Days Early/Late"] > 0 then 1 else 0

    The query I am trying to use for [Late Actions Count] is working but wherever [Date Closed] or [#"Days Early/Late"] = null, its giving an ERROR 'Expression.Error: We cannot convert the value null to type Logical.'

    Query giving error is: [Late Actions Count] = = Table.AddColumn(#"Filtered Rows", "Count of Late Actions", each if [#"Days Early/Late"] < 0 then "1" else if [Date Closed] = null then "1" else "0" )
    --If (column [Date Action Due] is before today's date (1/4/2018) and column [Date Closed] has a null value) then write 1, otherwise 0

    I know I'm very close but I am relatively new to PowerBI and PowerQuery, still trying to learn the basics. Any help would be greatly appreciated.

    Thank you in advance!


    • Edited by Ishan86 Thursday, January 4, 2018 11:39 PM found a few changes
    Thursday, January 4, 2018 10:05 PM

Answers

  • It looks to me like you are mixing up DAX and Power Query.
    Also your logic at NEW COLUMN LOGIC is incorrect as the action is late if days Early/Late < 0 (or the other condition).

    This query works with me:

    let
        Source = Table1,
        #"Added Custom" = Table.AddColumn(Source, "Late Actions Count", each if [Date Actions Due] < DateTime.Date(DateTime.FixedLocalNow()) and [Date Closed] = null or [#"Days Early/Late"] < 0 then 1 else 0, Int64.Type)
    in
        #"Added Custom"

    This is how it looks like in the query editor:

    Friday, January 5, 2018 2:34 AM