Text Field was Read as Scientific Number

[dprasetyo]

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

[kushanNa]

Hi @dprasetyo 

Have you tried to untick the following option ? 

https://community.fabric.microsoft.com/t5/Desktop/quot-How-to-prevent-Power-BI-from-converting-text-... 

[danextian]

Hi @dprasetyo 

Can you please  the original text strings as in  the data source so we can check this ourselves?

[dprasetyo]

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.

[v-aatheeque · Accepted solution]

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 ?

[v-aatheeque]

Hi @dprasetyo 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[v-aatheeque]

Hi @dprasetyo 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

[rajendraongole1]

Hi @dprasetyo - can you try below logic at PQ editor.

Number.ToText(Number.From([ID]), "F0") otherwise [ID])

check and confirm.

[Zanqueta]

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 🌀.

[dprasetyo]

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.

[Zanqueta]

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 🌀.

[AntoineW]

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

[dprasetyo]

thanks for the suggestion.
I've tried both but it still shows notation symbol.