Error to convert figures with single quote (') used as the thousands separator (e.g. 2'000)

[Fab117]

Hi,

In my Excel base, I've figure using single quote (') to highlight thousands.

When I connect this excel with Power BI, such numbers are generating errors.

Is there a way to solve this without doing change in the Excel?

NB: In Excel, format cells is:

Tks

Fab

[MFelix · Accepted solution]

Hi @Fab117 ,

Not sure what is the regional settings where you have the use of the ' as decimal but you can try to check the change type with locale:

However not sure if there is any option available what you can do before adding the format is to do a replace of the ' by nothing and then convert to number:

[Fab117]

Hi @MFelix ,

Thank you for looking at this challenge.

Here is outcome of your proposals:

1. Use of "Locale" parameters

Unfortunately, it doesn't work.

Initialy my data are presented as Text:

If I try to convert them into numbers using "Locale":

I still have the error mentionned in my initial post

=> would have been a good solution, but doesn't work.

2. Remove the single quote

It works.

As my numbers are "seen" as Text, as you proposed, I replaced in the column the single quote by nothing:

I get what I want, but as Text:

Then I can convert the column in the format I'm looking at

Thank you agin for your help

Fab