Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Las
Frequent Visitor

Converting Text Column having Text and Scientific Notation

Hi Experts,

I'm having a text columns with numbers, text and scientific notation, example as follows

 

202-203

2320042

AH3021

F21A-001

1.00E+11

 

In a situation like this how to handle the data and format it according to a more structured way?

 

Thank you.

Regards

Las

 

7 REPLIES 7
Las
Frequent Visitor

Thanks for your response.

Currently i'm using a formula =TEXT(<Cell>, "0") in Excel. Looking for a similar solution in Power Query, at the end should be able to convert the column to TEXT.  

 

Ta.

Las
Frequent Visitor

Thank for all your responses.

 

I might be looking for power query to convert the exponential values and rest of the other values in the column to text. The issue is after converting it will error out all the other values which is not having scientific notation. 

 

When having multiple values ( mix of text, SN and general types ) how to handle this situation?

 

Thank you

Regards,

Las

 

 

Power Query equivalent is Text.From like below

= Text.From([Column1])

Please post the output expected for the input given below

202-203

2320042

AH3021

F21A-001

1.00E+11

Hi Vijay,

 

Require everything in text format ( Yes, even numbers converted to text ). Currently we do it in Excel and upload it to PBI, this process is cumbersome due to large amount of data. 

 

Thank you.

v-jingzhang
Community Support
Community Support

Hi @Las 

 

In Power Query, data in a column should be changed to the same data type.

 

Currently this column is Text type. If you don't want to do any calculation or aggregation on this column, you can remain it as Text type. However, if you want to do some calculation, you can extract necessary data into a new column and change it to a specific data type. 

 

You can also use features like Pivot/Unpivot/Transpose... to change the table structure. We don't know the meaning of these text strings and what you want to do later, so we cannot suggest more on how to handle it. But ensure that the table should be a flat tabular structure and every column is expected to have data of the same single meaning.

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Vijay_A_Verma
Super User
Super User

It all depends upon what you want to downstream. Formatting is something which is not important in mashing up data unless needed. 

Hence, if in the downstream, you want to extract numbers, you can have following formula

= try Number.From([Column1]) otherwise Number.From(Text.Select([Column1],{"0".."9"}))

But it all depends what you want to do. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.