Forum Discussion
Text Field was Read as Scientific Number
Hi, I have a problem in showing pure text data type in Table kind of visualisation.
In my ID column, I have several rows consist of multiple IDs (that's why I set it to text data type in csv and in power query). It works in rows with multiple IDs (with some commas in it), but when it is a single ID then it becomes scientific notation. I've added a Text Check column which is a DAX formula of "ISTEXT(TableA[ID])" that returns True for all rows. It's strange that it still shows scientific notation for it.
Appreciate for your help and support.
Thanks
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 ?
12 Replies
Hi dprasetyo,
Can you check if the data type of the column ID is changed to TEXT with Power Query ?
You can force Text Rendering by creating a measure
ID_Display =
FORMAT ( TableA[ID], "@" )OR
ID_Display =
"" & TableA[ID]Use ID_Display only in visuals.
Hope it can help you!
Best regards,
Antoine
thanks for the suggestion.
I've tried both but it still shows notation symbol.
Hi dprasetyo
Thanks for sharing the image! This happens because Power BI visuals often auto-format numeric-looking text when rendering tables.✅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
Add a transformation that makes the value clearly non-numeric, for example:- Prefix or suffix the ID with a character (like a space or an apostrophe).
In Power Query, you can do: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 🌀.
Hi dprasetyo - can you try below logic at PQ editor.
Number.ToText(Number.From([ID]), "F0") otherwise [ID])
check and confirm.
thanks for the suggestion. I've changed the column in my csv into text data type and refresh my power query transformation, but it still showing scientific notation.
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 ?
