Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
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?
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