Forum Discussion
Power query help
Hi experts,
Getting below issue while changing data type in powery query
Power query is showing below value as 1.23457E+12 after changing data type in to Whole Number
| 1234567890123 |
No issue found after converting in to text format.
Hi Anonymous ,
Your data should show correctly in Power BI data view.
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([Column]),"G"))
You can play around with different formats, instead of General, like "D", "N", "#", "000000000000"
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , Seem like it decimal number in the database,
Try In power Query - Find out the first change type and make it decimal there
Or in column tools Use a scientific format
Hi Anonymous ,
Your data should show correctly in Power BI data view.
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([Column]),"G"))
You can play around with different formats, instead of General, like "D", "N", "#", "000000000000"
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
