Forum Discussion

jambrose's avatar
jambrose
Frequent Visitor
9 years ago
Solved

Finding Phantom Errors

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", ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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's avatar
jambrose
Frequent Visitor
9 years ago

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's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

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.