Cannot convert my numbers from text to decimal datatype

[Anonymous]

Hello

I have a csv file that contains numbers in Swiss format like 6'500.20 or 22.50 or 9'600.66.

When I try to change the type from text to decimal in the querry editor I get only errors.

The windows and power bi region settings are set to switzerland and should be correct, excel etc. is working fine with the numbers.

Spoiler

Region settings in windows:

Region settings for the imported file in Power BI:

Any idea what the issue could be?

[Anonymous]

Hi @Anonymous ,

I test to use optional parameter culture 'de-ch' but it still not works for ' characters.
Maybe you can try to add a custom column to remove thousand separator and convert remained text to number:

#"Added Custom" = Table.AddColumn(Source, "Custom", each Number.FromText(Text.Remove([G Number],"'")))

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous 

Thanks for your suggestions I will try if this works.

But is this a bug? I mean Excel and all other applications work fine with this number format. And PowerBI should be supporting the default offical swiss format for numbers right? And even if not supported specifaclly it should  be able to format the numbers based on the culture settings of windows (which are correct and match the number format)? Or am I wrong?

[Anonymous]

HI @Anonymous ,

If your system region and symbol has setting to swiss and these separator still not works, it may be a localization issue for swiss characters, you can contact to power query team to report this.
Regards,

Xiaoxin Sheng