Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Super User!
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
Just to note - in some of the answers it says to click Replace Values.
Replace Values is a combi-button, like a cross between a button and a drop down list. You need to expand it to get to "Replace Errors" specifically as opposed to just "Replace Values" which is on the standard button face.
Hope that helps someone.
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
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
Proud to be a Super User!
I am having the same issue. Unfortunately the proposed solution to replace errors with "null" does not work. My idea was to first change the error value in my table (#) to blank and then use the fill function to get the data from row above. Still, it does not work as Power BI does not replace errors with null.
In another dataset I have created workaround so that I created conditional column and some rules to pick up the date from one row above. But I suppose there should easier solution to this.
I need to reply my own post as I still continued testing this. Solution I found was to use the replace function in query editor already in the beginning of applied steps before the editor converted values as dates. I could replace # with null and the use the fill funactionality to replace blanks with the value from above.
I hope this helps also if someone is struggling with similar problem.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.