Forum Discussion

Aimeeclaird's avatar
Aimeeclaird
Icon for Helper IV rankHelper IV
6 years ago

IF Formula / Blanks / Dates

I have a set of data which is mainly dates. 

 

Here's an example of my data:

 

IDItem - Date Booked 1Item - Date Booked 2Item - Date Booked 3Item - Date Booked 4Date Job Passed
114/05/2020   02/04/2020
2 16/06/2020 17/06/202003/04/2020
312/05/2020   14/04/2020
4    20/04/2020
5  14/05/2020 25/04/2020

 

I want to be able to say, IF 'Item - Date Booked X' = BLANK(), show 'Date Job Passed' (for that row)

 

Ultimately, I want to say if the item has been booked (i.e. is not blank) then its good. If it is blank, show the data it was passed so I can count the days it has been sat there without being booked.

 

 

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Aimeeclaird it will make sense to unpivot your data

     

    - transform data
    - select id date job passed table
    - right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
    - close and apply

     

    add new column

     

    New Date = IF ( Table[Value] == BLANK(), Table[Date Job Passed], Table[Value] )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

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

    Hi Aimeeclaird ,

     

    You can create a new column in Power Query that picks up [Date Job Passed] if all other values are null, otherwise picks up the latest of the other dates like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY3BCcAwDANXKX4HLCtxOozJ/mu0oYU6gT70sHSyIsSkiDWFK0Hcx5EEKtoTjBLC17au6Jm3MxmoS6nOnD8DczmxbYuniAXxrb28pX/suAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Item - Date Booked 1" = _t, #"Item - Date Booked 2" = _t, #"Item - Date Booked 3" = _t, #"Item - Date Booked 4" = _t, #"Date Job Passed" = _t]),
    chgAllDateTypes = Table.TransformColumnTypes(Source,{{"Item - Date Booked 1", type date}, {"Item - Date Booked 2", type date}, {"Item - Date Booked 3", type date}, {"Item - Date Booked 4", type date}, {"Date Job Passed", type date}}),
    chgAllNumberTypes = Table.TransformColumnTypes(chgAllDateTypes,{{"Item - Date Booked 1", Int64.Type}, {"Item - Date Booked 2", Int64.Type}, {"Item - Date Booked 3", Int64.Type}, {"Item - Date Booked 4", Int64.Type}, {"Date Job Passed", Int64.Type}}),
    addMyDate = Table.AddColumn(chgAllNumberTypes, "myDate", each if List.Sum({[#"Item - Date Booked 1"],[#"Item - Date Booked 2"],[#"Item - Date Booked 3"],[#"Item - Date Booked 4"]}) = null then [Date Job Passed] else List.Max({[#"Item - Date Booked 1"],[#"Item - Date Booked 2"],[#"Item - Date Booked 3"],[#"Item - Date Booked 4"]})),
    chgBackDateTypes = Table.TransformColumnTypes(addMyDate,{{"Item - Date Booked 1", type date}, {"Item - Date Booked 2", type date}, {"Item - Date Booked 3", type date}, {"Item - Date Booked 4", type date}, {"Date Job Passed", type date}, {"myDate", type date}})
in
    chgBackDateTypes

     

    In Power Query, go to New Source>Blank Query, then in Advanced Editor paste over the default code with my code above to follow the steps I took to do this.

     

    This gives me the following output:

     

     

    Apologies if I've misunderstood your requirement, I didn't find it that clear.

     

    Pete