Forum Discussion
Text Field was Read as Scientific Number
Hi dprasetyo
Is the scientific notation appearing in Power Query Editor, in the data view, or only in a report visual?
After changing the column to Text, did you apply the change in Power Query and click Close & Apply?
Is there any step in Power Query (for example Changed Type or Added Custom Column) that might be converting the value back to a number?
can you share the error screenshot or sample CSV data file ?
Hi dprasetyo
✅Why does this happen?
- Power BI tries to interpret text that looks like a number as numeric for display purposes.
- Even if the data type is Text in the model, the Table visual can apply auto-formatting when the string contains only digits (and is long enough to resemble a large number).
How to Fix It: Force Text Formatting in Power Query
- Prefix or suffix the ID with a character (like a space or an apostrophe).
Table.TransformColumns(
Source,
{{"ID", each "'" & Text.From(_), type text}}
)
If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
thanks for the suggested solution.
I've tried in power query and it shows additional "'" in there, but when I put it in the table viz, it goes back showing the E notation.
Hi dprasetyo
Perhaps you are opening CSV files directly in Excel instead of trying the following:
Open a blank Excel file → Import Data → CSV.
When opening in Excel, use Import Data and set the column to “Text” to avoid automatic conversion.If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.