Forum Discussion
Data Cleanup - Datatype any to number
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?
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 processedHere 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 spaces2. 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.
4 Replies
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 processedHere 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 spaces2. 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.That worked perfectly! Thank you!
- dharmendars007
Memorable Member
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.
