cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Replace Errors values with Null values

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.

2 ACCEPTED SOLUTIONS
Super User I
Super User I

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

 

.time.PNG

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

 

time1.PNG

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

 

time2.PNG

 


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Super User III
Super User III

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

 

View solution in original post

2 REPLIES 2
Super User III
Super User III

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

 

View solution in original post

Super User I
Super User I

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

 

.time.PNG

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

 

time1.PNG

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

 

time2.PNG

 


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors