Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Manage funky number formats importing csv file

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 trie...
Need Help
BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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