Power Query help replace date with zero or null

[cool_333]

Hi all,

I was wondering if you gurus can help. I'm trying to replace all the different dates that appear in the value column with either 0 or null whichever works in the power query. Thank you in advance.

[latimeria]

Hi @cool_333 ,

Try this, using Table.TransformColumns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBLDsUgDAPvwjpS8wGSd5aK+1+jTwWp9tIzFii+76Z+aV6uHk1aVVvyZwHMquTgjniUxMYDsOvpTuxmytw46eUuvnFRO6Rv/AOcdv4zxfIXXmfojB1eCuF1dHGwo7M7u0GTsKMJJjva4QtrPQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ValueIni = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Value", each try Number.FromText([ValueIni]) otherwise Date.FromText([ValueIni])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ValueIni"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}),
    #"===Start here" = #"Changed Type",
    #"Replaced Date" = Table.TransformColumns(
        #"===Start here",
        {
            {"Value", each if Value.Is(_, Date.Type) then null else _ }
        }
    )
in
    #"Replaced Date"