DataFormat.Error when converting numbers with non-breaking spaces

[ItsProtosup]

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 is supposed to be of type number, but some values are imported as text with invisible characters (non-breaking spaces, Unicode , etc.), which causes the conversion to fail.

I’ve tried using Text.Remove, Text.Select, and even try ... otherwise, but these either return null or hide the error without fixing it. I don’t want to lose the data — I want to recover the actual number, not replace it with null.

Example of problematic value: "2 047" → I want to get 2047 as a number.

Any help or clean workaround would be greatly appreciated!

[v-agajavelly]

Hi @ItsProtosup ,

Were you able to try out the Power Query step with Character.FromNumber(160) to clean up the non-breaking spaces and convert your values to numbers?

Just wanted to check if that solved the issue for you, or if you’re still hitting any errors when changing the column type.

Regards,
Akhil.

[ItsProtosup · Accepted solution]

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,

[v-agajavelly]

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.

[ItsProtosup]

I tried using the formula Text.Select(Text.From([Quantité commandée]), {"0".."9", " ", "-", ","}) to extract only digits, spaces, dashes, and commas from the "Quantité commandée" column, keeping everything as text to avoid conversion errors. This was meant to clean the data without transforming it into a number.

I also applied these two transformations to remove invisible or problematic spaces:
= Table.ReplaceValue(#"Type modified", "#()", "", Replacer.ReplaceText, {"Quantité commandée"})
= Table.ReplaceValue(#"Valeur remplacée", " ", "", Replacer.ReplaceText, {"Quantité commandée"})

Unfortunately, none of these approaches worked — the same error still appears in the column.

[danextian]

Hi @ItsProtosup 

Create a custom column that picks only the numbers and some characters (period and comma)

Text.Select([column], {"0".."9", ",", "."})

[rohit1991]

Hi @ItsProtosup 

This error happens because the numbers contain non-breaking spaces (Unicode 160) instead of normal spaces, so Power Query reads them as text.

The easiest fix is:

• In Power Query, select the column >> Replace Values >> in “Value To Find” paste the special space (you can copy it directly from one of your values like 2 047) >> leave Replace With empty.

• After that, change the column type to Number and it will work.

If you prefer using a formula, you can also do:

Text.Replace([Column], Character.FromNumber(160), "")

then convert to number.

This way "2 047" will correctly turn into 2047.