Forum Discussion
A numeric looking text column cannot be converted to number
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.
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.
9 Replies
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.
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.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.
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.
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?
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.
Hi basaknilabja
Please follow below Steps to fix in Power Query:
1. Open Power Query Editor
Go toTransform Data.
2. Remove non-numeric characters (keep digits, dot, and minus)
= Text.Select([YourColumn], {"0".."9", ".", "-"})
3. Trim and clean
Even afterText.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!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.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
