Power Query extract function based upon specific text and returning specific number of characters

[jadempsey]

I am transforming a data set and extracting a series of numbers which need to be in the final format of ####-####. Every row will contain this format of numbers and each number will start with 083 plus an additional number (3/5/ or 7, I will use # as the variable to indicate the 4th digit).  I need to extract the four digits before the hyphen, the hypen itself and the four digits following the hyphen.

The data set is messy and non standardized.  I removed any letters from the field and it has been trimed and cleaned to reduce the number of spaces to contend with.  That said some entries that start with 083#, some with other numbers.  Various entries have hyphens before the 083# series, and most have numbers following the 4 digits following the hyphen designated for retention.

In the refernce table below I am looking for an output  from the first column of 0831-2612.

Any advice would be greatly appreciated.

0170-0831-26122
0831-7677230
017820831-2316
017810831-2257
017820831-2470
017810831-2207
70-0831-25941
01700831-43662
01700831-41492
01700831-46532
01700831-40942
01700831-50931
01700831-44133
017020831-5172
01700831-50201
01700831-48392
01700831-53031
01700831-54072
01700831-57862
01310831-54981
0831-62583
01700831-60351
01700831-55412
01700831-62901
01700831-58553
01700831-58611

[jadempsey]

Not ot be a pain, but I am on a work machine and not eager to open attachements from unknown sources.  Would you be kind enough to break out the file into text?  As a plus that will also help others who may need help with this issue.

Thank you

[serpiva64]

Hi,

i extracted text after delimiter

then added a custom column

then extracted first characters from Column1

and finally merged in the right order

[jadempsey]

This is great and we are almost there.  The 0831 code could also be 0833 or 0835 or 0837.  Can we do the same thing by using 083 as the delimiter and then adjusting the count code somehow?

[serpiva64 · Accepted solution]

Hi,

you can do almost the same

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZDLDcMwDEN3ybkBRFE/zxJk/zWa1q2LKkc/PNK0j2MTpOxSxK4B1e18XOx1zMhUygTI0ikRsRAmUs9uWcrNkmmt63wYvpK8kTFCG4KNjsLZkQxryGWw1xvIheZSR96CKj1Y7COc0uvd5NaVtR5EfKxR+P1yqBf/QyH0Xu2GVh06+kwvd3YUuKzzCQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Extracted Text After Delimiter" = Table.TransformColumns(Source, {{"Column1", each Text.AfterDelimiter(_, "083"), type text}}),
#"Added Custom" = Table.AddColumn(#"Extracted Text After Delimiter", "Custom", each "083"),
#"Extracted First Characters" = Table.TransformColumns(#"Added Custom", {{"Column1", each Text.Start(_, 6), type text}}),
#"Merged Columns" = Table.CombineColumns(#"Extracted First Characters",{"Custom", "Column1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merged Columns"

i extracted text after delimiter 083

then added a custom column 083

then extracted 6 first characters from Column1 

and finally merged in the right order

[serpiva64]

Hi,

you can obtain your result

by applying the steps in the attached file

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!