How to extract/clean up numbers from column value. Same column!

[tmyasoutov]

Hey everyone!

One column in Power Query has two types of values:

A 73 digit and a 8 digit values. End result must be 8 digit, 10016711 and 10016709. How to extract or clean up the 73 digit values starting with PO: ?

[dcrosseto · Accepted solution]

Thanks for the info, here is my suggestion for the output, would love to see others alternatives.

test is the column name I was trying

steps

1. checking if string starts with "PO"
2. if True I do two RemoveRange, one for removing the first 4 chars "PO: "
3. second the rest of the chars after the 8th number of what you want
4. i use the Lenght to get numbers of string subtracting the "PO: 12345678" 

I created a Custom Column on PowerQuery, but you can also add this directly on Source

if Text.StartsWith([test], "PO") 
   then Text.RemoveRange(
             (Text.RemoveRange(Text.From([test]),0,4)),
             8, Text.Length([test])-12
             )
else [test]

Please check if works, hope it was helpful.

Regards,

Douglas.

[dcrosseto]

Hi @tmyasoutov ,

I don't know for the others, but for me it's quite confusing your expectations just with this image.

Could you please create like 3 fake example something like this, it'll be very helpful

Actual value	Expected final value
ABC333333ABC	333333
ZYX999999ZXC	999999

Regards,

Douglas.

[tmyasoutov]

Hey @dcrosseto ! Apologies for the confusion, here's how it looks like in the slicer:

Really long out of place lines, where I only need the 1001XXXX numbers at the start, rather than the whole line!

10016710

PO: 10012174 A9B: XXXXXXXXXXX Desc: XXXXXXX_XX_XXX_XXXXX-XXXXXX_XXX WBS: