Date Calculations, Null, and Text Mess

[jamiers]

Hello All,  I have got myself turned all upside down and sideways.  I am trying to figure out how to accomplish the following mission and I keep getting hung up because I can't find videos that are specific to my issue or am not merging them together properly.  My data shows below:

Issues

1. My data file pulls over a field that contains a driver's license expiration date.  In the data above, here are the pulled-over fields:
   • account_id - number
   • DL Type - Text
   • Expir Date - Text
2. I "split column by delimiter" so that I could create the date in yyyy-mm-dd format so that it could be sorted as text (later, I will discuss putting words in this field).
3. DL Exp Date.1, DL Exp Date.2, DL Exp Date.3 are the split by delimter fields.
4. DL Exp Month is a custom field with the following code (inserting a 0 prior to make the sort work)
   • = Text.PadStart( Text.From([DL Exp Date.1]), 2, "0" )​
5. DL Exp Day is a custom field with the following code (inserting a 0 prior to the contents to make the sort work).
   • = Text.PadStart(Text.From([DL Exp Date.2]), 2, "0")​
6. DL Exp is a text column that includes
   • = Table.AddColumn(#"Added Custom1", "DL Exp", each Text.Combine({Text.From([DL Exp Date.3], "en-US"), [DL Exp Month], [DL Exp Day]}, "-"), type text)​

Here is what I want to do.

In the DL Exp column, I want to be able to:

1. If null is in the Expir Date field, I want the words to show "No DL Expiration Date on File" in the DL Exp column
2. If the Expir Date is older than TODAY, I want to show the word "DL Expired" in the DL Exp column
3. If the Expir Date is TODAY or Newer, Show the yyyy-mm-dd date that is in DL Exp column.

Now, I am not adverse to creating a second column in the Power Query, but when all of this comes together in the Power BI report, I want there to be one column that has one of these things:

• Current DL Expiration Date that is TODAY or Newer in yyyy-mm-dd (no regional/locale changes because I use US date formats always).
• "DL Expired" words if the Driver's License is expired
• "No DL Expiration Date on File" words if the field shows as null (which is an issue because Date Calculations can't deal with null)
• The Power BI report will also pull a blank cell if there is no account_id matching (which I do not think needs to be addressed here).

As you can see... I'm all turned upside down/sideways... and hoping someone can help me think through this a little bit.  I have been working on this for ~90 minutes now... and am just confused about a path forward.

[ronrsnfld · Accepted solution]

I think you are confusing things by bringing in text conversions in other columns initially. It is generally much simpler to work with dates as the date data type, then as text strings which have to be converted back and forth.

Starting with the three columns that you are obtaining from your data source, just add a Custom column that performs the logic for that column:

eg 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcm7DcAgDEXRXVwj+Y9NnTEQRVbI/kUoEEGRnl5xT+/AQZquUOC6n/lMyIZC0mCUyaapsTVQ5oh9YRX50JH9wAxlr1tXVK3ZfjHImeyI4wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account_id = _t, #"DL Type" = _t, #"Expir Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"account_id", Int64.Type}, {"DL Type", type text}, {"Expir Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DL Exp", 
        each if [Expir Date] = null 
                then "No DL Expiration Date on File" 
                else if [Expir Date] < Date.From(DateTime.LocalNow()) 
                then "DL Expired" 
                else [Expir Date])
in
    #"Added Custom"

or, if the date in the "DL Exp" column for some reason related to data presentation **must** be as text in that format, then:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcm7DcAgDEXRXVwj+Y9NnTEQRVbI/kUoEEGRnl5xT+/AQZquUOC6n/lMyIZC0mCUyaapsTVQ5oh9YRX50JH9wAxlr1tXVK3ZfjHImeyI4wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account_id = _t, #"DL Type" = _t, #"Expir Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"account_id", Int64.Type}, {"DL Type", type text}, {"Expir Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DL Exp", 
        each if [Expir Date] = null 
                then "No DL Expiration Date on File" 
                else if [Expir Date] < Date.From(DateTime.LocalNow()) 
                then "DL Expired" 
                else Date.ToText([Expir Date],"yyyy-MM-dd"), type text)
in
    #"Added Custom"

[jamiers]

Thank you -- this will give me ideas for the future too.