Forum Discussion
Power Query extract function based upon specific text and returning specific number of characters
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 |
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
5 Replies
- serpiva64
Solution Sage
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!
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
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?
