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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.