I pulled a worksheet with large numbers into Power Query to split them into different columns. Unfortunately, they turned scientific in the transfer (E+12) and I cannot find how to turn them back to regular whole numbers. I cannot split them as is because some of them are E+11 and some are E+12 so the split ends up in the wrong place for some of them. What are my options?
Thank you
Hi Benedikte,
Indeed, long numbers are not properly handled in Power Query, I had the same problem with UPC codes, which are basically a 12 digit number.
You have 2 options:
-one option is to format the column as Whole Number or decimal numbers, as needed, then from Transform tab, Text Column section, Format the column as lowercase (uppercase, propercase, trim or clean will also work). I know it does not make sense, but it works...
-The second option is to add a new column with a formula to format the original column to General format, using the G switch:
=Number.ToText(Number.From([#"Product UPC/EAN"]),"G"))
You can play around with different formats, instead of General, like "D", "N", "#", "000000000000"
