Convert text to number

[kfschaefer]

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.

[EMIDH]

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. 

[bashhwu]

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.

[newvie]

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

[Greg_Deckler · Accepted 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

[Anonymous]

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?

[PBInewbee123]

Hi,

did you found a solution for this issue? I am having the same problem now.

Thanks

[kfschaefer]

Thanks I found this answer at the same time you posted.

K

[Nargiz1977]

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

[kcantor]

Select the column in your data model and change the data type in the ribbon.

 

[S184019]

@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?