Only get numeric, Decimal numbers or null from the type of any column

[PowerOn_DP]

Hi All,

I have a table in which a column has any type and it has text, numbers, decimals like 0.98 or < 15 days or 15%

I tried to create a custom column using below code but it is not working as I am trying to get.

Text.Select(Number.ToText([Monthly Target New]),{"0".."9","."})

Here is how the data looks

In these both cases, I need 2 and 15 

Please help

[v-janeyg-msft · Accepted solution]

Hi,  @PowerOn_DP 

You can modify your code to delete data that is not a number and decimal point, there are many ways.

Like this:

= Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Monthly Target New],{"0".."9","."}))

or 

= Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Monthly Target New], List.Transform({1..45,47,58..126}, each Character.FromNumber(_))))

 

Reference:Text.Remove - PowerQuery M | Microsoft Docs

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[aj1973]

Hi @PowerOn_DP 

You don't need coding to do it. Use replace value to do it in Power Query (Select column then right click). Then change the type of the column.