Issue with Type Conversion and CSV?

[Gjakovasc]

I've got some trouble with type conversion. CSV flat contains column named rocket cost and it is written in format 50.0 or 75.0 or 1,130.0 etc. So it is clearly english format, where , is thousand sepeartor and . is decimal separator. When I try to load it into Power BI I get text file (beacuse I'm working on polish local settings where there is no . in numbers). So I want change regional settings for this column as USA. But unfortunatly I get strange thing, acutally I see numbers but from 50.0 it changes to 50,0. That's ridiciolous and not understandable for me. Can any1 help? Maybe if I stream my screen for you, you will see what's going on more clearly. I need perhaps 3 minutes of your time so if any1 can help I would be very very thankful

[ray_ux · Accepted solution]

@Gjakovasc 
There's culture applied
1. on import, in power query. If not specified it defaults to the current system
2. after importing the value there's a display culture that doesn't have to mutate the underlying data type. Like date-times are integers/floats under the hood, but you can set them to display in any text format.

You can use the optional culture parameter set to en-US Which tells it use that locale/culture's rules for converting text to numbers.

   #"Numbers from en-US" = Table.TransformColumns(
        Source,
        {
            {"Number", each Number.FromText(_, "en-US"), type number}
        }
    ),

hopefully this solve the problem.