A numeric looking text column cannot be converted to number

[basaknilabja]

A numeric looking text column cannot be converted to number as I tried but it can't be solver even after Text.Select Cleaning and Number.FormText. So I can't use in visual as its not showing the numeric value and beheaves like text field.

[V-yubandi-msft]

Hi @basaknilabja ,

Did you get a chance to check out the response I sent earlier? If it solved your problem, you might want to mark it as the accepted solution. It'll help others in the community who might have the same issue.

Thanks 

[V-yubandi-msft]

Hi @basaknilabja ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[V-yubandi-msft]

Hi @basaknilabja ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[basaknilabja · Accepted solution]

I apologize for the delayed response due to my busy schedule. I'm experiencing an issue with a measure in Power BI. There's a relationship between the 'Value' field in the 'Time_period' table and the 'Value' field in the 'Master_Table'. However, when I try to use the measure to fetch the selected value from 'Time_period'[Value] independently, it returns a blank value.

When I break the relationship then and its working fine. No need to convert the value field in Master_table into numeric value.

[V-yubandi-msft]

Hi @basaknilabja ,

Thank you for your patience, and I appreciate the clarification.

I tested the scenario using sample data and confirmed that when there is a relationship between Time_period[Value] and Master_Table[Value], a measure like SELECTEDVALUE(Time_period[Value]) may return blank due to filter context.

To resolve this, I used the following DAX to ignore the relationship filter from Master_Table.

TestMeasure = CALCULATE(SELECTEDVALUE('Time_period'[Value]), USERELATIONSHIP('Time_period'[Value], 'Master_Table'[Value]))

This allows the selected value from Time_period to be returned regardless of the relationship.

Based on this test (see attached screenshot), the measure now behaves as expected and meets your requirement.

FYI:

I hope this helps...

— Yugandhar
Community Support Team.

[grazitti_sapna]

Hi @basaknilabja 

Please follow below Steps to fix in Power Query:

1. Open Power Query Editor
Go to Transform Data.

2. Remove non-numeric characters (keep digits, dot, and minus)

= Text.Select([YourColumn], {"0".."9", ".", "-"})

3. Trim and clean
Even after Text.Select, some invisible characters might remain. So also apply:

Transform > Format > Trim
Transform > Format > Clean

These options remove whitespace and control characters.

4. Convert to number

Transform > Data Type > Decimal Number or Whole Number

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[V-yubandi-msft]

Hi @basaknilabja ,

Thank you for engaging with the Microsoft Fabric Community. We appreciate the helpful insights shared by the @ryan_mayu  and @SreeniBattula .

Please review the suggestions provided, as they accurately address the issue. If you need any further clarification feel free to let us know.

Yugandhar

Community Support Team.

[ryan_mayu]

@basaknilabja 

what's the error message after you use Number.From?

if there is no error message, have you tried to change the data type in column tools?

[SreeniBattula]

You can convert a text column containing numbers into a numeric data type using Power Query Editor

Open Power Query Editor,Click on Transform Data in Power BI Desktop to open the Power Query Editor.

Select the Text Column,Click on the header of the column that contains the numeric values stored as text.

Change Data Type,In the Transform tab, click on Data Type.

Choose the appropriate numeric type (e.g., Whole Number, Decimal Number, or Currency) from the dropdown menu.

Power Query will attempt to convert the text values to numbers.

Note: If the column contains any non-numeric text values, Power Query may return errors or null values for those rows.