cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerOn_DP
Helper I
Helper I

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

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

PowerOn_DP_0-1618592177964.png

In these both cases, I need 2 and 15 

Please help

 

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

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(_))))

 6.png

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.

View solution in original post

2 REPLIES 2
v-janeyg-msft
Community Support
Community Support

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(_))))

 6.png

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.

View solution in original post

aj1973
Community Champion
Community Champion

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.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors