Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tmyasoutov
Frequent Visitor

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

Hey everyone!

 

One column in Power Query has two types of values:

 

2022-11-04 14_24_13-LEH_Q_PROCEEDIX - Power Query Editor.png

 

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: ?

1 ACCEPTED SOLUTION
dcrosseto
Resolver II
Resolver II

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.

 

View solution in original post

3 REPLIES 3
dcrosseto
Resolver II
Resolver II

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
Resolver II
Resolver II

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 valueExpected final value
ABC333333ABC333333
ZYX999999ZXC999999

 

Regards,

Douglas.

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

 

2022-11-04 15_03_35-LEH_Q_PROCEEDIX - Power BI Desktop.png

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:

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.