Manage funky number formats importing csv file

[Anonymous]

Hi community,

i'm getting strange number formats like "10. Apr" when importing csv file. the number should be "10.04" and it works for some numbers in the same column like 28.05 or 12.00.

I tried different encoding codes (1252, 65000, 65001, 12000) while importing csv, but it hadn't worked.

I need only decimal formats in the column. Does somebody has any idea?

Thanks a lot 

[BA_Pete]

Hi @Anonymous ,

When you import your CSV file, Power Query will try and 'guess' the data type based on the first X number of rows (specified in the CSV import dialog). This will create a 'Changed Types' step in your APPLIED STEPS list on the right in PQ. something like this:

You can delete this step to revert your query to the raw data imported.

You can change/prevent this behaviour altogether by going to File > Options & Settings and adjusting the following:

To apply globally to all reports:

To apply to only current report:

Pete

[Anonymous]

only one thing that helped was to change country settings on in windows settings to en-US

[Anonymous]

Hi Pete, 

thanks for your reply.

i already deleted the data type detection und have manually routine to manage necessery data types. To set the options globally didn't work. Do you know any other options applied when opening csv in excel? the wrong formats appears already when opening csv in excel. Only in text (Notepad) i get the right data.

Thanks

Ksenia

[BA_Pete]

Hi @Anonymous ,

When you first import the CSV using Get Data, you actually specify the detection at that point. You should set the following option to 'Do not detect data types':

Pete