cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Joshua_Lehmann Frequent Visitor
Frequent Visitor

Cannot convert my numbers from text to decimal datatype

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

 

Error

 

Region settings in windows:

Region-Format

Region settings for the imported file in Power BI:

Region-Format-Power-BIFile

Any idea what the issue could be?

3 REPLIES 3
Community Support Team
Community Support Team

Re: Cannot convert my numbers from text to decimal datatype

Hi @Joshua_Lehmann ,

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],"'")))

5.png

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Joshua_Lehmann Frequent Visitor
Frequent Visitor

Re: Cannot convert my numbers from text to decimal datatype

Hi @v-shex-msft 

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?

Community Support Team
Community Support Team

Re: Cannot convert my numbers from text to decimal datatype

HI @Joshua_Lehmann ,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 243 members 2,779 guests
Please welcome our newest community members: