Forum Discussion

SHAKEDALROY's avatar
SHAKEDALROY
Helper I
5 years ago
Solved

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....
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    5 years ago

    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's avatar
    Jimmy801
    5 years ago

    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

     

Jarkko's avatar
Jarkko
Frequent Visitor
3 years ago

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.

SundarRaj's avatar
SundarRaj
Super User
1 month ago
Hi Jarkko 
Here’s another approach using  try … otherwise  that may be useful for you, and for anyone else who runs into the same issue.

let
Source = Source,
Type = Table.FillDown(
Table.TransformColumns(
Source,
{{"Times", each try Time.From(_) otherwise null}}
),
{"Times"}
)
in
Type
 
This removes the need for a separate  Changed Type  step and keeps the conversion and error handling in the same query. It also preserves the option to fill down the  Times  column afterward.

Thanks,