Excel Query, Some Number Cannot Convert to Number

[MAS42]

Dear Community.

I've seen a few posts similar to this (https://answers.microsoft.com/en-us/msoffice/forum/all/excel-query-some-number-cannot-convert-to-num...) but so far none that solves the problem.

I've been using Power Query/PBI for around 10 years and have never experienced this issue.  I'm scraping the 1st table of data from a webpage (https://www.ne.ch/autorites/DFS/SCCO/impot-pp/Pages/Baremes.aspx - note the same issue arises if I simply copy/paste values directly to Excel),  However, while I'm able to convert some of the % values to number, there are always a few that give an error,  even after removing the % sign, cleaning, trimming, etc.

If anyone has encountered something similar and fixed this I'd be interested in how they did this.

Regards

Martin Short

[Anonymous]

Hi @MAS42 ,

Based on your description, it appears that you encountered an error when attempting to change the data type of the column [Taux de chaque categorie] to Number. Despite applying the replace value, clean, and trim transformation steps, the error persists. Please try the following methods to resolve the issue:

• Paste Special: Select the problematic cell values, then press Ctrl + X. Right-click on the destination cell and choose "Paste Special" -> "Values" to paste the cleaned data as values only.

• Manual Inspection: Manually inspect the problematic cells to identify any visible or non-visible discrepancies compared to the cells that successfully convert to numbers.
• Re-type Values: Re-type one of the problematic cell values and check if its data type can be successfully changed to Number.

If none of the above methods resolve the issue, could you please click "Error" in the screenshot below and share the error details with us? This will help us diagnose the problem and provide a solution. Thank you.

Best Regards