Power query error caused by thousand values from CSV

jjidriesse

New Member
Joined
Apr 20, 2017
Messages
2
Hi,

Since a couple of days I'm working with Excel 2016 and power query.

I was trying to import a CSV file with sales data, but with changing the sales data from text to numbers, numbers above 999 returning an error. This is caused by a blank space between the thousand values, but I can't figure out how to get rid of this.

I've tried to trim the data in power query and Excel itself, also with ctrl+h (tried to replace blank space by nothing) and substitute. Unfortunately without any success.

Hopefully anyone can help.

Kindest Regards Job
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
I was able to replace the space with , and then convert to Currency.

Here's the code that was generated.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Field1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type"," ",",",Replacer.ReplaceText,{"Field1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Field1", Currency.Type}})
in
#"Changed Type1"
 
Upvote 0
Power Query allows to provide a culture code (or Locale) when converting text to numbers.
A lot of culture codes use a space as thousands separator and a decimal comma, e.g. French (fr-FR).
Few cultures do the same, but with a decimal period (dot), e.g. Switzerland - French (fr-CH).

Example: #"Changed Type with Locale" = Table.TransformColumnTypes(#"Trimmed Text", {{"Sales", Currency.Type}}, "fr-FR")

So the best way to import and transform the csv file is to find out the Locale/culture that was used to create the csv file and use the same code with the transformations in Power Query.
 
Last edited:
Upvote 0
Marcel

I was looking for something along those lines, but being new to Power Query I couldn't find it so used the method I posted to do the conversion.

Had another look a minute a go and found it.:)
 
Upvote 0
Hi Marcel,

Thanks a lot for your quick reply and the given solution. I'm working for a French company, so the culture code was the magic key for Excel.

Best Regards Job
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top