Data Cleanup - Datatype any to number

[stiani]

Ive got a column "belop" where i try to remove "spaces" and make the column type number. But as you can se only numbers up to 999 will register as "number". The other as text.

Ive tried to make a function "remove spaces" 

= (InputText as any) =>
let
Cleandata = Text.Combine(
List.Select(
Text.Split(
Text.Trim( InputText ), " "),
each _ <> ""
),
" "
)
in
Cleandata

This did not work. Ive also tried replace value " " with "" in transform. 

Ive also tried to just change data type to "whole number" or "fixed desimal number" then the output is this:

And the error message is this 

Ive also tried replace value  "," with ".".

How do i  transform column belop to numbers?  

[v-jtian-msft · Accepted solution]

Hi,dharmendars007 ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,@stiani .I am glad to help you.
Based on your description, you have a text column that needs to be processed

Here is my test data(Text type with spaces)

The text here is formatted as a number plus space plus decimal point (I interpret the comma in the screenshot you provided as a decimal sign)
You need to convert it to a numeric type (signed decimal type)
If my understanding is correct, you can refer to my test below
1. Split the question column into multiple columns based on spaces

2. Select the multiple columns that have been split (after multiple selections), merge them into one column, and successfully remove the spaces.

3. Set the data type correctly to numeric (decimal)

4. Correctly format the data for the modified field in desktop (keep two decimal places)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stiani]

That worked perfectly! Thank you!

[stiani]

It did not work  as intended.

For values above 999 they addes 2 desimals at the end.

For values below 999  i am getting an error message for the values below.

[dharmendars007]

Hello @stiani , 

Please try creating custom column using the below formula..

Text.Select([Belop], {"0".."9", "-"})

If you find this helpful , please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN