Forum Discussion

itsmeanuj's avatar
itsmeanuj
Helper IV
2 years ago

Filter out Text in Date column

Hi,

 

I want to filter out all the values that do not look like dates in a column in Power Query. Here is the sample data.

 

N/A
6/1/2019
12/4/2018
7/1/2020
TBC
10/26/2021
Nov_18

 

In the above example, I want to filter out N/A, TBC, and NOV_18. is there an automated way to do that in power query? 

 

Regards,
Anuj

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • CoreyP's avatar
    CoreyP
    Solution Sage
    2 years ago

    You could just change the data type to Date, then remove errors.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8tN3VIrViVYy0zfUNzIwtARzDI30TUA8CzDPHCxlZADmhDg5Q5QY6BuZgYQNwVy//LJ4kPJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Column1"})
in
    #"Removed Errors"
  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello itsmeanuj ,

     

    you could remove it from power query by clicking the drop down arrow for the filtering and uncheck it. thus it won't show.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi itsmeanuj 

    You can put the following code to advanced editor in power query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8tN3VIrViVYy0zfUNzIwtARzDI30TUA8CzDPHCxlZADmhDg5Q5QY6BuZgYQNwVy//LJ4kPJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try Value.Is(Date.FromText([Value]),type date) otherwise null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.