Date column contains text and would like to turn it to null

[akhaliq7]

I have a date column that contains date values but some rows also contain text values. I want to replace all text values to null. Using Power Query and the M language ideally.

I have thought of some solutions but the code is returning errors

Solution 1:

= Table.SelectRows(Table, each if Text.Length([Column1]) > 0 then null else [Column1])

this is returning a error message:

Expression.Error: We cannot convert the value #date(2022, 1, 20) to type Text.
Details:
Value=20/01/2022
Type=[Type]

[slorin]

Yes, you're right

x = record (the entire row) 

if "x[Column1].type = text" then null else x[Column1]

Stéphane

[akhaliq7]

thanks

[slorin · Accepted solution]

Hi

Change type to date then replace error with null

= Table.ReplaceErrorValues(Table.TransformColumnTypes(PrevStep,{{"Column1", type date}}), {{"Column1", null}})

Or replace text with null

= Table.ReplaceValue(PrevStep,each Type.Is(Value.Type([Column1]),type text),null,(x,y,z)=>if y then z else x,{"Column1"}) 

Stéphane 

[akhaliq7]

Can you explain the (x,y,z) => if y then z else x

i get y is the logic type.is

z is null 

but not sure what x is i am guessing that is the value that exists already in that column beforehand