Scientific Number in Text Field

[knagarlmet]

I have a text field showing numbers in scientific notation. Can anyone advise how to correct this to show the highlighted number at the bottom please?

• To clarify, this needs to be text column as it needs to join to another table (the field it is joining to is working as expected, showing the full number
• I have tried converting to decimal out of curiousity and it doesn't solve the problem
• As you can see in the Applied Steps, this is before any transformations have happened
• It is appearing correctly in the source data (SQL Server)

[v-pnaroju-msft]

Hi knagarlmet,

Thank you for your update and for sharing your insights and approach towards resolving the issue. Kindly continue to utilize the Fabric Community for any further assistance with your queries.

Thank you.

[v-pnaroju-msft]

Hi knagarlmet,

We want to check if the information we gave helped fix your problem.If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[knagarlmet · Accepted solution]

We ended up changing the data type in the source data to a numeric field, as nothing seemed to work in Power BI unfortunately. Thanks to those who offered suggestions! Must be a techincal issue.

[v-pnaroju-msft]

Hi knagarlmet,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @Greg_Deckler, @danextian, for your response.

Hi knagarlmet,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

From my understanding, the issue arises because Power BI’s Query Editor automatically detects long numeric strings and may incorrectly interpret them as numbers, displaying them in scientific notation even when the original data is text. To retain the complete value without scientific notation and to ensure proper functionality for table joins, kindly follow the steps outlined below which may help resolve the issue:

1. In the Power Query Editor, right-click on your column and select Change Type > Text.
2. If the value still appears in scientific notation, add a new custom column using the following formula:
   = Text.FromBinary(Text.ToBinary(Text.From([code], "en-US")))
   

   This approach compels Power BI to treat the value as plain text and prevents numeric formatting.

3. Remove the original column if it is no longer required, and then load the corrected data.

   Additionally, please navigate to File > Options > Data Load, and uncheck the option "Detect column types automatically" to avoid this issue in future.
   
   We hope that the information provided will assist in resolving the issue. Should you have any further questions, please feel free to reach out to the Microsoft Fabric Community.

   Thank you.

[danextian]

Hi @knagarlmet 

It's werid that the value is shown in scientific notation even though the data type is text. What's even werider is that the numbers appear italicized and right-aligned just like how numbers are typically displayed in the query editor. I have created a dummy table in sql containing a long string of numbers and set the data type to number. Power Query displays it correctly. Have you tried explicitly changing the data type yourself as text instead of relying on the default schema from the database? Otherwise, try these custom columns

Text.From([Column])


Text.From([Column]) & ""

[knagarlmet]

Hi @Greg_Deckler. Thanks. Yes, glad it's not just me confused by this. Any advice or things I should try?

[Greg_Deckler]

@knagarlmet Use Advanced Editor to look at the code and maybe insert a step to specifically change the column to text? I don't know, that's really, really strange.

[Greg_Deckler]

@knagarlmet That's strange because the column header says ABC which is text but the values are formatted as numbers, italicized and right justified. This is how it should look as a text column: