Forum Discussion

jessica-r-next's avatar
jessica-r-next
Frequent Visitor
4 years ago
Solved

MAC Address CSV Scientific Notation

I have CSV files that when importing into PBI, the MAC IDs display as scientific notation. Changing the type to text doesn't help. I find that in Excel, only formatting as text -during import- (impor...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi jessica-r-next ,

     

    If your data in the source is displayed in scientific notation, then the data will be lost when you convert it to text in Power Query (i.e. the last few digits will be zero). Based on my testing, I think you have to set the column's type to Text in the CSV file.

     

     

    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

When you open the file in PBI, at the bottom of the Dialog, instead of "Load", select "Transform Data".  The Power Query editor will open.  Delete the #"Changed Type" line and set the data types for those columns to text  (or edit it in the formula bar or Advanced Editor). Then Load it back into PBI.

  • jessica-r-next's avatar
    jessica-r-next
    Frequent Visitor
    4 years ago

    I do that, but then it changes it to the scientific notation view as text which actually strips out some data and replaces it as all 0s. 

    • ronrsnfld's avatar
      ronrsnfld
      Super User
      4 years ago

      Following my directions, I cannot reproduce your problem.

      With the data samples you show above, the transformation =>

      I suspect you are doing something different from what I demonstrated.

       

      Did you delete the #"Changed Step" step before setting the data type to text?