Record has some values as a period or text and need to convert all to number format

[unclejemima]

I have a table containing the Skid qty of an item.  The records have either a period "." if there is no information, or a number (example 495) stored as text.

I'm trying to covert the record to a format "Whole number" (its currently text format) , so I can do a calculation on it.

If I try and convert by changing the drop-down "text" data type to "whole number" I get an error "Cannot convert value "." of type text to type Integer." because some of the records have a period "." or some text to them.

I tried doing an "If >0" statement, but that does not work either.  I think I need to remove all the "." and anything that is NOT a number from the actual query but I'm not sure how.

I tried a "value" formula as well and it does not appear to work either.

Help please 🙂

[unclejemima]

Ok...this is being interesting.

I tried to make new custom column in the Query editor with this formula...

= Text.Select([String],{"0".."9"})

It successfully took only the number 0 to 9 out of any text... and now I'm still getting the error "Cannot Covert Value " of type Text to Type Integer.

Isn't there an IFERROR type statement I can use to fix this?

Any suggestions?

Thanks!

[v-kkf-msft]

Hi @unclejemima ,

Does your problem have been solved? 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,

Winniz

[v-kkf-msft · Accepted solution]

Hi @unclejemima ,

I added the column in the same way and was able to convert it to Whole number format successfully. 

Try the following code to see if it works.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQitWJVkpMMoIyklOM08AsvWIIBSZNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Column1],{"0".."9"})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[HashamNiaz]

Hi @unclejemima !

You can go to Transform Data Power Query editor, can choose Replace transformation to replace "." with 0 and other non-numerical values to 0. Then you can perform the Text to Int conversion transformation.

Hope this will help you solve the issue.

Regards,

Hasham

[unclejemima]

This would be a clever idea as you suggest...only the master table containing those records has valid text fields that I'm not using for this particular situation but would like to remain as text as the master table is used for other queries.

Basically, there is a table with say 1 million records, I'm extracting around 50k records with specific criteria to a reduced list in a new coloumn (via LOOKUP formula) and then those results are mainly numbers, with a few of the silly "." thrown in.

That said, can I trim my LOOKUP to only retrieve valid numbers not text or any other clever way of doing this?