Convert a column with numbers and text values to decimal values

[Anonymous]

Hi

I have an issue with converting the data type of a column. The column contains numeric values (decimals) and one text value (NA). I want to keep the NA's as that accurately represents the nature of the data. However, when I try to convert this column to fixed decimal, it returns an error for all text values. 

Is there a way that I can keep NA's and convert numeric values into fixed decimal type? 

Thanks!

[Icey · Accepted solution]

Hi @Anonymous ,

Just as what @az38 mentioned, it is not supported in Power BI.

One workaround, create a measure like so:

Measure = 
VAR Data_ = SUM('Table'[Column1 - Copy])
RETURN 
IF(ISBLANK(Data_),"N/A",Data_)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[az38]

@Anonymous  no, there is no such way to get text value as any number

[amitchandak]

@Anonymous , one idea is to  extract number from it and mark the new column as decimal column. You have to add code to get decimal too

https://eriksvensen.wordpress.com/2018/03/06/extraction-of-number-or-text-from-a-column-with-both-text-and-number-powerquery-powerbi/