CONVERT TEXT TO NUMBER

[cuohanele]

Is there a way to change a text datatype to a number? Having issues with that. Maybe a way to bypass the error message that a text can't be converted to a number. I've tried advanced editor. Please help. Thanks!

[Anonymous]

This method worked for me: 

in the Transform data view, i Duplicated the original column, (under Add Column Menu, select the column, Duplicate Column menu item) , then Transform menu item the new column, make Data Type numeric. Numbers stayed behind as numeric and text items became blanks, which can be filtered out in the report. 

[Anonymous]

[Anonymous]

another way could be this when MS will exstend the feature of list.sort  to tablesort.

 

[Anonymous]

Try padding the "numbers" with trailing blanks so that all text ha same length.

Think like you would do some sort of right alignment

Ps

If you search the forum can find a post of mine with the code to do that

[Ajinkya369]

Hi @cuohanele ,

Can you just share me the sample dataset and the .pbix file so ,that i can figure out the problem and provide you the solution.

Thank You

[artemus]

Can you provide a sample format of the numbers you are trying to parse along with the country code you are running power bi in?

[cuohanele]

I created a dynamic format for a column in my table to be filtered by a specific slicer. Unfortunately, it is sorting the numbers like they are string. I used the code below:

Sales$ = IF(ISCROSSFILTERED('Parameter'[calculations]),

SWITCH( TRUE(),

    VALUES('Parameter'[calculations]) = "YoY%",

SWITCH( TRUE(),

ABS([Sales$ YoY%]) < 1, FORMAT([Sales$ YoY%],"0.0%"),

ABS([Sales$ YoY%]) >= 1, FORMAT([Sales$ YoY%],"0%"),

BLANK()

),

    VALUES('Parameter'[calculations]) = "YoY Gap", CONCATENATE("$", FORMAT([Sales$ YoY Gap],"0,0")),

    VALUES('Parameter'[calculations]) = "Current Year Actuals", CONCATENATE("$", FORMAT([Sales$ CY],"0,0")),

BLANK()), BLANK ())

When any of the options in the slicer is picked, it returns this column sorted out of order. 

So it is sorting something like this:

100

1

2

200

222

3

33

It's formating as a text but I want it to format as a number

Thanks in advance!

[artemus]

oh, this is a DAX question.

Just change the data type of the measure to number:

[cuohanele]

That's the issue. It is greyed out. Used DAX ro crossfilter column to the slicers but the datatypes are different for all three dropdown options. One is percent and the others are currency. I can't switch them all to the same datatype

[v-juanli-msft]

Hi @cuohanele 

Create a dax column to convert text column to number:

text to number = CONVERT([text column],INTEGER)

Then sort by this column to get proper order.

Or create another column besides your [Sales$] column,

then display [Sales$] column on your visual, but sort by the following column

Sales$ 2 =
IF (
    ISCROSSFILTERED ( 'Parameter'[calculations] ),
    SWITCH (
        TRUE (),
        VALUES ( 'Parameter'[calculations] ) = "YoY%", SWITCH (
            TRUE (),
            ABS ( [Sales$ YoY%] ) < 1, FORMAT ( [Sales$ YoY%], "Fixed" ),
            ABS ( [Sales$ YoY%] ) >= 1, FORMAT ( [Sales$ YoY%], "Fixed" ),
            BLANK ()
        ),
        VALUES ( 'Parameter'[calculations] ) = "YoY Gap", CONCATENATE ( "$", FORMAT ( [Sales$ YoY Gap], "Fixed" ) ),
        VALUES ( 'Parameter'[calculations] ) = "Current Year Actuals", CONCATENATE ( "$", FORMAT ( [Sales$ CY], "Fixed" ) ),
        BLANK ()
    ),
    BLANK ()
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[artemus]

You can add a new column (which doesn't depend on that one) which is numbers and can be set as the sort order for you main column