cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SHAKEDALROY
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
Nathaniel_C
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

Jimmy801
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
Jimmy801
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

Nathaniel_C
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors