Forum Discussion

lj910628's avatar
lj910628
Regular Visitor
5 years ago
Solved

Mixed Date and Text Extract

I have the following mixed column containing status and date formatted as text. The date is entered when complete i.e. status is complete. I would like 2 additional columns, 1 to assign a stat...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxTCM8vylaK1YlWCkjNS8nMSwezDUz1Dcz1jQyMDCFcE31DYwQXWZehgb6hIUjKQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PIA Form" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Status", each if Text.Contains([PIA Form], "/") then "Complete" else [PIA Form], type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "CompletionDate", each if [Status] = "Complete" then Date.From([PIA Form]) else null, type date)
in
    #"Added Custom1"

     

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxTCM8vylaK1YlWCkjNS8nMSwezDUz1Dcz1jQyMDCFcE31DYwQXWZehgb6hIUjKQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PIA Form" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Status", each if Text.Contains([PIA Form], "/") then "Complete" else [PIA Form], type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "CompletionDate", each if [Status] = "Complete" then Date.From([PIA Form]) else null, type date)
in
    #"Added Custom1"

 

Pat