Finding Phantom Errors

[jambrose]

I'm importing data from a CSV file with minimal manipulation.  Had a few errors converting "Inf/-Inf" to a decimal which I fixed by replacing the offenders with the appropriate "Infinity/-Infinity", seems to work fine now.  When I next refreshed the data, I got the message "396,385 rows loaded.  3,026 errors."  However, as far as I can determine, these errors do not exist.  Clicking the "View errors" link gives me "This table is empty." in the query editor preview.  I tried Table.SelectRowsWithErrors, and it comes up empty.  I tried try[Column] on every single column in the table, all of them came up clear of errors.  The only thing I can think of at this point is that it might still be reacting to the previous error I fixed, but I don't know why that would happen.  How can I go about finding these phantom errors or make Power BI realize there aren't any?

Edit - The same warning appears even if I add the command Table.RemoveRowsWithErrors()...

[jambrose]

With further inspection, it does seem to be complaining about the fixed rows - the numbers match exactly.  I'm able to finally dismiss the errors if I remove the Replace Value step earlier and instead do a Replace Error step.  This is still not ideal because I have positive and negative Infinity values, and I want to keep the distinction between the two, which I can't do with Replace Error.  I can do it fine by Replace Value after the import, but then it complains of the error that no longer exists.  Is there anything I can do here, or should I just ignore the error after import/refresh?

[MarcelBeug]

In order to avoid errors when loading the query results, all values should have the same type as the column types.

E.g. if you replace #infinity in a number column by the text "Infinity", then this is no error for Power Query, but it will be an error when loading the data.

Possibly you can change the column type to "any" or adjust your replacements so the new values will comply with the column type.

[jambrose]

@MarcelBeug - I did do the replace before the type conversion.  My goal being that the type conversion would interpret them as whatever the equivalent value is in the decimal number format.  Is there a different way I should be doing this?  Unless the format doesn't have a representation for infinity at all...that would be annoying, but I could probably do something ugly to get around it.

[KHorseman]

I would make a new column in the query editor to replace the one you're using:

= if [NumberColumn] = "-Infinity" then Number.NegativeInfinity else if [NumberColumn] = "Infinity" then Number.PositiveInfinity else [NumberColumn]

Set the data type to number, delete the old column, load.

Edit: or Marcel's method. I like that one.

[MarcelBeug · Accepted solution]

It's difficult to tell without your code / example data.

Just to illustrate: the following code:

let
    Source = {1..10},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Number"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",4,-1/0,Replacer.ReplaceValue,{"Number"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",8,1/0,Replacer.ReplaceValue,{"Number"}),
    MyTable = Table.TransformColumnTypes(#"Replaced Value1",{{"Number", type number}})
in
    MyTable

Returns, after loading in Power BI Desktop, without any errors reported:

[jambrose]

@MarcelBeug - Ah perfect, this is what I was missing.  I was trying to convert the literal text value "Infinity", and it wasn't getting there, but 1/0 is working.  Thanks!