Extract dates and numbers

[Mederic]

Hello everyone,

I would like to get the result in the screenshot below without using the "Column From Examples" option
Thanks in advance for your help

Regards

Value

>01/07/2023

And > 31/08/2023

<01/10/2022

And <01/04/2023

<=2500 €

<01/05/2023

And <01/01/2023

And >5000 €

<01/01/2023

And <0

[Mederic]

Hello @PhilipTreacy 

Thanks very much, it's perfect 😊
Have a nice day

[PhilipTreacy · Accepted solution]

Hi @Mederic 

Download example PBIX file

This query will give you the desired result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiik1MDBONTDUNzDXNzIwMlaK1YlWcsxLUYBIKBgDZSwQMmDRZKByQwOQoBGqcpCEgQm6alsjUwMDhUdNa1BNMDDFYiFYwhCbS4BmYDMEm9pkA6XYWAA=", 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", "Date", each if Text.PositionOf([Value], "/") >= 0 then Text.Select([Value], {"0".."9", "/"}) else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Amount", each if Text.PositionOf([Value], "/") = -1 then Text.Select([Value], {"0".."9"}) else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}, {"Amount", Int64.Type}})
in
    #"Changed Type1"

regards

Phil