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

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.