Cannot replace error with null

[kobkabnaja]

Hi community,

Apologies for what might seem like a basic question, but I am facing an issue converting text entries to null values in my dataset. While I’ve reviewed multiple examples online, I still can’t seem to apply them to my specific dataset—there might be another underlying issue causing this.

In the attached picture, I am trying to convert the highlighted column to whole numbers by removing rows with text entries. I have attempted to handle the errors by going to Transform > Replace Values > Replace Errors and setting them to null. However, the text errors persist in the dataset anddo not change to null values as expected.

Here is the specific error I recieved:

DataFormat.Error: We couldn't convert to Number.
Details:
72130620;72130617;72130618;72130619

If anyone has any suggestions or advice on resolving this, I would really appreciate it!

Thank you!

K

[ZhangKun]

You should notice that the last line is full of errors. This is because the data is not parsed correctly. For example, the following code demonstrates a similar situation：

Table.ReplaceErrorValues(#table({"A", "B"}, {{1,2 }, {1}}), {{"B", 3333}})

The solution to this problem should be to ensure that the data can be parsed correctly, or use Table.RemoveLastN to discard the last row.

[Anonymous]

Hi @kobkabnaja ,
Based on your description that the conversion of errors to nulls is not performed as expected, you can try using the following code to create a new column to perform the conversion

= try Number.FromText([YourColumnName]) otherwise null

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

While it's difficult to see the values in your picture, it appear that there is an error in the column to the left, which will persist. Why not split that column to the left by the ";" delimiter, choosing "Split to new rows" in the advanced options? Then you can convert to numbers without having to filter. 

--Nate

[kobkabnaja]

Thanks, @Anonymous , for the quick response!

It turns out I got an error across the entire row. After looking into the details, I found that the highlighted column contains these problematic values.

Would you recommend changing this entry, "72130620;72130617;72130618;72130619," to something else temporarily, and then converting it back to a whole number?

Thanks!

K

[ronrsnfld]

1. Which is the first Applied Step that shows that error?

2. Can you create a data sample, as text which can be easily copy/pasted, that can be used to reproduce your problem. Change any confidential information to something random but that still has the same format and data type as your original.