Convert text to number

[Anonymous]

Hi everyone,

I would like to create a conditional formatting based on a text column. My data column contains a four digit base number. For example 1A1A, 1A1B,1A1C. 1A1A is smaller than 1A1B. Power BI can sort the column correctly, but I cannot use this column directly, since the data needs to be a number for the power bi to determine the minimum and maximum values. Thus, is there any way to assign a number ranking according to the base number column?

Your answer is well appreciated. 

[v-henryk-mstf · Accepted solution]

Hi @Anonymous ,

According to your description, I think the easiest way is to create a column index based on the cardinality column as a sorting. Or use the "Replace Values" function in power query to replace the text in the column with a number, and then change the column type to a number.

and you can read following document:

Table.ReplaceValue - PowerQuery M | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tex628]

Hi, 

I'd say you need to create an additional column, which is completly numeric. Replace A with 01 and B with 02 and so on. 

You can do this either with a long Text.Replace statement or by creating a mapping table in excel which you then merge into the original table. 

/ J