Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Good Day Everyone,
I am trying to extract a specific format of: ##a# or (number)(number)(letter)(number)
I need first 4 parts of the serial number. I've attached examples of source data and what I'd like to extract:
Does anyone have any ideas? Really stuck trying to get a reliable formula for this.
Thanks Everyone!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY4xCgIxEEWvMqQ2kMxMJoldomAhpLERYgqFLRa0Wdn7q5scwPLNPPivVmVNEiaPqu2quhSDhyCGqeO0zPcnlPX1mBaY37AHS+cYkXjo30MsJOy8BLitxqCAxZML/ip+c6xAAgkkfQFRZ+8Cx05WZybNTrprtpR8HPQrSyMtjecfw6q1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial #" = _t]),
pattern = {false, false, true, false},
chars = List.Buffer({"0".."9", "A".."Z"}),
finder = (t as text) =>
[lst = List.Buffer(List.Select(Text.ToList(t), (x) => List.Contains(chars, x))),
gen = List.Generate(
() => [ch = lst, okay = false, serial = {}],
(x) => not List.IsEmpty(x[ch]) or x[okay],
(x) =>
[okay = List.Transform(List.FirstN(x[ch], 4), (w) => (try Number.From(w))[HasError]) = pattern,
serial = if okay then List.FirstN(x[ch], 4) else {},
ch = List.Skip(x[ch], if okay then 4 else 1)],
(x) => Text.Combine(x[serial])
),
z = Text.Combine(List.Select(gen, each _ <> ""), ", ")][z],
out = Table.AddColumn(Source, "Desired Outcome", (x) => finder(x[#"Serial #"]))
in
out
Hello @agc1234,
same result as @Vijay_A_Verma but different approach.
See line four. This one also matches the criteria.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY4xCwIxDEb/SuhsoU3StHVrFRyELi5C7aBww8G5nNz/V68dX/Lge7Uqa5IweVTtUNWtGDwFMUwdp3V+LlC292taYf7AESxdY0Tiof8OsZCw8xLgsRmDAhYvLvi7+N2xAgkkkPQFRJ29Cxw7WZ2ZNDvprtlT8nnQvyyNtDSeqrUv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial #" = _t]),
UppercasedText = Table.TransformColumns(Source,{{"Serial #", Text.Upper, type text}}),
Ad_Cleaned = Table.AddColumn(UppercasedText , "Cleaned", each Text.Select([#"Serial #"], List.Buffer({"0".."9", "A".."Z"}))),
Ad_EachFourCombination = Table.AddColumn(Ad_Cleaned, "Each 4 Combination", each List.Accumulate(
{0..Text.Length([Cleaned])-1},
{},
(s,c)=> List.RemoveNulls(s & { try Text.Range([Cleaned], c, 4) otherwise null })
), type list),
Ad_OnlyMatching = Table.AddColumn(Ad_EachFourCombination, "Only Matching", each List.RemoveNulls(List.Transform([Each 4 Combination], each
if (try Value.Is(Number.From(Text.ToList(_){0}), Number.Type) otherwise false) //1st digit check if is number
and (try Value.Is(Number.From(Text.ToList(_){1}), Number.Type) otherwise false) //2nd digit check if is number
and (try not Value.Is(Number.From(Text.ToList(_){2}), Number.Type) otherwise true) //3rd digit check if is text
and (try Value.Is(Number.From(Text.ToList(_){3}), Number.Type) otherwise false) //4th digit check if is number
then _ else null)), type list),
Ad_FourDigits = Table.AddColumn(Ad_OnlyMatching, "4 digits", each Text.Combine([Only Matching], ", "), type text),
RemovedOtherColumns = Table.SelectColumns(Ad_FourDigits,{"Serial #", "4 digits"})
in
RemovedOtherColumns
Hi @agc1234 ,
Another approach
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Serial#", type text}}),
#"Added Desired Output" = Table.AddColumn(
#"Changed Type",
"Desired Output",
each
List.Transform(
Text.Split(Text.Remove([#"Serial#"],{" ", "-"}),"&"),
each Text.Start(
Text.RemoveRange(_,
0,
Text.PositionOfAny(
_,
{"0".."9"},Occurrence.First
)
),
4
)
)
),
#"Extracted Values" = Table.TransformColumns(
#"Added Desired Output",
{"Desired Output", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
)
in
#"Extracted Values"
Use below code
Note - In row 4, 87X6 is also a pattern matching your requirement.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY4xCwIxDEb/SuhsoU3StHVrFRyELi5C7aBww8G5nNz/V68dX/Lge7Uqa5IweVTtUNWtGDwFMUwdp3V+LlC292taYf7AESxdY0Tiof8OsZCw8xLgsRmDAhYvLvi7+N2xAgkkkPQFRJ29Cxw7WZ2ZNDvprtlT8nnQvyyNtDSeqrUv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Serial #" = _t]),
#"Added Custom2" = Table.AddColumn(Source, "Custom.1", (x)=>
let
p = Text.Select(x[#"Serial #"], {"0".."9", "A".."Z"}),
c = Text.Length(p)-3,
r = List.Generate(()=>[i=0, a=Text.Start(p,4)], each [i]<c, each [i=[i]+1, a=Text.Middle(p,i,4)], each [a])
in
Text.Combine(List.Select(r, (x)=> List.ContainsAll({"0".."9"}, Text.ToList(Text.Start(x, 2) & Text.End(x, 1))) and Text.Select(x, {"A".."Z"}) = Text.At(x,2)), "; ") )
in
#"Added Custom2"
Hi @agc1234 ,
In Power BI, when we want to process complex and unordered data, adding many steps using power query may not get the desired results. As we know, power query cannot use regular expressions. If we call Python script and use regular expressions to process the data this time, it will become simple. Please refer to
Using regular expressions in power bi desktop - Microsoft Fabric Community
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
27 | |
12 | |
12 | |
11 | |
9 |
User | Count |
---|---|
53 | |
28 | |
17 | |
14 | |
13 |