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.
Accepted Solutions