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.

jbwtp's avatar
jbwtp
Memorable Member
4 years ago

Hi jessica-r-next , few example IDs would help (maybe modify them a bit to make it fake). From what I know MAC IDs are hexadecimal and look like 2C:54:91:88:C9:E3 or 2c-54-91-88-c9-e3.It is hard to see how this can be assumed a number to convert to a scientific format.

Thanks,

John

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

5825670256e3

4825680256e4

 

The source data doesn't display the MACs in the hexadecimal format and instead displays them as above. 

  • jbwtp's avatar
    jbwtp
    Memorable Member
    4 years ago

    Hi jessica-r-next,

     

    I quite agree with ronrsnfld, there seems to be something unusual with your data. I've created an csv file with your data and tested it on PBI and Excel/PQ. Both can be fixed by removing the Changed Type  step in the query....

    Could you please (1) check and confirm that the source data in your csv file are not already in the scientific format like 123E+3 format; (2) share your code [remove the address if your csv file from the code for security/privacy], so we could model/test it on our side.

     

    Thanks,

    John