Mixed Date and Text Extract

[lj910628]

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 status (In Work, Pending, Complete) and another to extract the date.

Can anyone help? DATEVALUE doesn't seem to play.

[V-pazhen-msft]

@lj910628 

Check the example pbix for detail:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ30jcyMDJQitWJVvLMUwjPL8oGswNS81Iy89LBbCN9E6AiQ0swx0TfGKHDUN8QxDFUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Column", "Column - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"Column - Copy", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column - Copy", "Date"}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Renamed Columns", {{"Date", null}}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Errors", "Status", each if [Date] = null then [Column] else null)
in
    #"Added Conditional Column"

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[mahoneypat · Accepted solution]

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

[watkinnc]

Do you want dates down the whole column?  I'm having difficulty envisioning what your final table should look like.

--Nate