Forum Discussion
DataFormat.Error when converting numbers with non-breaking spaces
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,
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.
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,