Forum Discussion

ItsProtosup's avatar
ItsProtosup
Frequent Visitor
1 year ago
Solved

DataFormat.Error when converting numbers with non-breaking spaces

Hi everyone, I'm facing a frustrating issue in Power Query when trying to convert a column to number type. Some values like "2 047" (with a non-breaking space) trigger this error:  The column...
Need Help
Tips and Tricks
  • ItsProtosup's avatar
    ItsProtosup
    11 months ago

    Hi Akhil,

    Yes, I did try using "Character.FromNumber(160)" to remove the non-breaking spaces, but that wasn’t the actual cause of the issue.

    After some investigation, I found that the problem was related to the SharePoint source implementation level being set to 2.0. This level tries to automatically convert columns to types (text, number, date, etc.). It correctly transforms dots into commas in my case, but doesn’t remove the spaces between thousands and hundreds, which caused an error during the automatic column type conversion.

    The solution was either to revert to implementation level 1.0 — which requires manually reformatting all columns (changing types, replacing spaces and dots with commas, etc.) — or to disable the “Thousands separator” option directly in the SharePoint list settings.
    I couldn’t find any other workaround while staying on implementation level 2.0.

     

    Thanks again for your help!

    Best regards,

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi ItsProtosup ,

This happens because your values contain a non-breaking space (Unicode 160) instead of a normal space, which Power Query can’t handle when converting to number.

Here’s the simplest way to fix it in a robust way.

  1. In Power Query, go to Advanced Editor or Add Custom Column.
  2. Use this formula to remove the special space and convert to number:
= Table.TransformColumns(#"PreviousStep",{{"Quantité commandée", each Number.FromText(Text.Replace(Text.From(_), Character.FromNumber(160), ""), // remove NBSP"fr-FR" // keep French number format (comma as decimal) ), type number}})
  • Character.FromNumber(160) specifically removes the hidden NBSP character.
  • "fr-FR" ensures 1 176,20 converts to 1176.20 correctly.
  • No data loss, 100% safe for all rows.

After applying this, you can safely change the column to Number type, and it will work without errors.

Thanks,
Akhil.