Power query error caused by thousand values from CSV

[jjidriesse]

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

 

[Norie]

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"

 

[MarcelBeug]

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.

 

[Norie]

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.

 

[jjidriesse]

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