Replace Errors values with Null values

[SHAKEDALROY]

Hi,

I'm trying to replace Errors with Null values but  it's a Time type column so it doesn't let me to do so.

Is there any other way to replace it?

Help will be greatly appreciated.

[Nathaniel_C · Accepted solution]

Hi @SHAKEDALROY ,
Created a table with a bad row.

.

It was text, but made it time, error showed.

Went to the Transform tab, clicked on Replace Values, entered NULL and it worked even as Time column.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Jimmy801 · Accepted solution]

Hello @SHAKEDALROY 

another approach is to not change the column type blindly but to make a Table.TransformColumns instead to check whether the data is a datetime or not. Here an M-code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVMzTSMzJQMDSyMjBQitUBCllAhQwsYEJOOYlApBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type text}}),
    ChangeToDateTime = Table.TransformColumns(#"Changed Type",{{"DateTime", each try DateTime.From(_, "de-DE") otherwise null , type datetime}} )
in
    ChangeToDateTime

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Nathaniel_C · Accepted solution]

Hi @SHAKEDALROY ,
Created a table with a bad row.

.

It was text, but made it time, error showed.

Went to the Transform tab, clicked on Replace Values, entered NULL and it worked even as Time column.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel