Forum Discussion
Convert text to number
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.
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
10 Replies
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,
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.
kcantor What if the column doesn't yet exist. What if for example, i am building a summary table in dax?
Summary = SUMMARIZE(PreSummary,PreSummary[Date].[Month],PreSummary[Date].[Year],"Column_1",calculate(Sum(PreSummary[somecolumn1])),"Column_2",calculate(sum(PreSummary[somecolumn2])),"Column_3",calculate(sum(PreSummary[somecolumn3])),"YRMO",CALCULATE(VALUE(PreSummary[Month])), <--I want to treat this like a number."OLDTotal",CALCULATE((sum(PreSummary[Total])))))The back story is that I HOPE to be able to sort columns in a matrix by a number. That number here is the YEAR concatenated with the 2 digit month that way I know it will stay in order. But I must "Summarize" that table somehow. Thoughts?
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.
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
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.
