Forum Discussion
Finding Phantom Errors
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 MyTableReturns, after loading in Power BI Desktop, without any errors reported:
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?
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.
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.
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 MyTableReturns, after loading in Power BI Desktop, without any errors reported:
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!
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.