Does anyone know how to restrict sorting to only valid numbers?

[ABNHS84]

My organisation releases data that has to include small number suppression, which is an asterisk.

This is a problem across all data analysis software I've come across, but wondered if there was a trick within Power BI to allow sorting largest to smallest (as opposed to alphabetically).

It works fine when I set all asterisk values to blank(), however we are duty bound to show the asterisks to indicate that a number has been suppressed. As soon as I allow them in, and sort by that column the numbers are organised alphabetically:

1

10

100

2

20

200

etc. Which is useless.

Is it possible to tell the table visual to treat string values as a certain number, then treat the whole column as numbers when sorting?

Any help much appreciated!

[v-jiascu-msft]

Hi @ABNHS84,

Could you please mark the proper answers as solutions?

Best Regards,
Dale

[ABNHS84 · Accepted solution]

Thanks Dale, 

Your advice wasn't quite what I was looking for, but it did lead me to the correct solution.

I created a new column in my table with code

My table = selectcolumns(
<My other column codes>,
"ColumnSort", If(OriginalColumn = "*", BLANK(), Value(ThisTable[OriginalColumn])),
)

This allowed me to sort the 'Text' colunm as if it were numerical column.

[v-jiascu-msft]

Hi @ABNHS84,

Power BI has a trick indeed. Please refer to the snapshot below. You just need to add a numeric column to indicate the orders.

\

Best Regards,
Dale