Oct 25 2021 05:55 AM - edited Oct 25 2021 05:56 AM
Let us suppose that I wish to load the following American CSV into an European workbook:
Company,Q1,Q2,Q3,Q4
Microsoft,20.3,30.5,23.5,40.3
Google,50.2,40.63,45.23,39.3
Apple,25.4,30.2,33.3,36.7
IBM,20.4,15.6,22.3,29.3
Of course both all separators are wrong. We use semicolons instead of colons to separate values and commas instead of dots for decimal separator. There are many way to do that, but I was experimenting a specific one: Get Data.
If I use Get Data, I get the right columns but the decimal separator is ignored:
So I click on Transform Data to get the Power Editor.
First I change the data type of all columns but the first one to Decimal number, replacing the current transformation (Whole Numbers). Second, I open the advanced editor to get
let
Source = Csv.Document(File.Contents("D:\Docs\#Accompany\Customers\Mondadori\Piani dei corsi\CSV con virgole.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company", type text}, {"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", type number}})
in
#"Changed Type"
and change
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company", type text}, {"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", type number}})
to
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company", type text}, {"Q1", type number}, {"Q2", type number}, {"Q3", type number}, {"Q4", type number}}, "it-IT")
I expect now that decimal numbers are recognized, but they are not. Why?
PS Please, do not reply by explaining to me alternatives to do the same. I want to understand why this method does not work.
Oct 25 2021 06:49 AM
SolutionAdding the culture you say to Power Query locale of File Origin, not the destination. In your case origin locale is US one, thus it shall be used.
Oct 25 2021 10:20 AM
@Sergei Baklan Wow... it works! Thank you, Sergei.
Oct 25 2021 10:21 AM
@Dario_de_Judicibus , glad it helped
Oct 25 2021 06:49 AM
SolutionAdding the culture you say to Power Query locale of File Origin, not the destination. In your case origin locale is US one, thus it shall be used.