Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
What is the proper syntax to convert a text into a number format?
I need to convert some import data(xls) that for some reason is coming accross as text instead of a number. It is formatted in xls as a currency, but in BI it is a text field. FieldName("PEPM Revenue"), needed to be a number due to summarizing the field.
Thanks.
Solved! Go to Solution.
Couple different methods, you can choose number or currency from the Modeling tab in the Data Type section and it will generally convert it just fine. There is also the VALUE function:
https://msdn.microsoft.com/en-us/library/ee634584.aspx
With most of my tables, changing Data Type under Structure in the Column Tools tab (it appears when selecting a column in Data view) does not work. I then use Power Query which I find under Home, Queries, Transform Data.
In Power Query, changing Data Type under Transform, Any Column, Data Type typically works better.
If the the values of the column took the format of text with currency such as positive values $123,23.05 or negative values ($32,256.09), I would replace the character $ by 0 by right clicking the column and selcting "replace values".
Then you can now convert the format of resulting column to a fixed decimal number.
do you have a solution now? I need help about that too. I have a column where in it consist of letters and numbers. How can I convert the string to numbers? I need to sum up the numbers. Please help
Couple different methods, you can choose number or currency from the Modeling tab in the Data Type section and it will generally convert it just fine. There is also the VALUE function:
https://msdn.microsoft.com/en-us/library/ee634584.aspx
Hi,
Had the same issue but the problem is it when changing the format from text to numbers it removes any decimel points? so if the number is for instance (2,28) when text, it become's (228) after changing the format to number. Any suggesitons?
Hi,
did you found a solution for this issue? I am having the same problem now.
Thanks
Thanks I found this answer at the same time you posted.
K
exl file uploaded as a text. I try to convert text to number but unsucsessfuly. Could anybody advice why exl converted to text in POWER BI after upload, and how to convert text to value
Select the column in your data model and change the data type in the ribbon.
Proud to be a Super User!
@kcantor What if the column doesn't yet exist. What if for example, i am building a summary table in dax?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |