SOLVED

Why "it-IT" does not work in Table.TransformColumnTypes

Occasional Contributor

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:

get_data.png

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.

 

 

 

3 Replies
best response confirmed by Dario_de_Judicibus (Occasional Contributor)
Solution

@Dario_de_Judicibus 

Adding 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.

@Sergei Baklan Wow... it works! Thank you, Sergei.