MAC Address CSV Scientific Notation

[jessica-r-next]

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- (import wizard) works to preserve the MAC IDs as is needed. But I cannot find a way to force PBI to import the column as text, not change after import. 

Is there a way, perhaps in a blank query, to force PBI to keep the MAC IDs in their correct format? To bring them in as text (not change them later)?

[v-kkf-msft · Accepted solution]

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.

[jessica-r-next]

As much as I was fearing this is the case, I do believe this is correct. I have to walk back what I said in my original post of getting import wizard to work between csv and excel. It worked for some, but not all. So, clearly my issue is at the source document. 

I appreciate everyone's help on this. 

[ronrsnfld]

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]

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]

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?

[jbwtp]

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]

5825670256e3

4825680256e4

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

[jbwtp]

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