Forum Discussion
Extract six-digit numbers from text string
Hi Anonymous ,
You can use this regular expression:
let str = ""; let regex = ^[0-9]{6}$; let match = str.match(regex); if (match) { console.log(match); }If this expression still doesn't work with your data you can use this link to automatically generate a regular expression.
You can refer to the following documents to learn how to use regular expressions in power query:
Regular expressions in Power Query – Q-stat
Using regular expressions in power bi desktop - Microsoft Fabric Community
Using Regular Expressions(RegEx) in Power BI | by Shivam Shukla | Medium
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) Click "transform data" to enter the power query editor, open the "Advanced Editor" and copy and paste the following code.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMta11DUyUjA0sTIxtzIy1fXKz8hTcMlP1QjPL8pWyMsvSS3WVAhyDQx1DQ7RNTEzNDcwUCguTcrNLClJTVGK1cFviGNKSmZJZn5eYo5Ccn5ubmpeCapppqYGOmDKHEJZIsyOySPDdVQxLzg1J003sbg4Mz0vNUWhJF8htDi1SCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Comments = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Comments", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Comments", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Comments"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Comments", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "Comments", Splitter.SplitTextByPositions({0, 19}, false), {"Comments.1", "Comments.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Comments.1", type text}, {"Comments.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each Text.Combine(
List.RemoveNulls(
List.Transform(
Text.ToList([Comments.2]),
each if Value.Is(Value.FromText(_), type number)
then _ else null)
)
)),
#"Split Column by Position1" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByRepeatedLengths(6), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Custom.1", Int64.Type}, {"Custom.2", Int64.Type}, {"Custom.3", Int64.Type}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type3", {{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}, "en-US"),{"Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Comments.1", "Comments.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged.1")
in
#"Merged Columns1"(3) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks, this is really interesting. Unfortunately, the Split Column by Position step isn't necessarily going to work as the comments aren't always formatted exactly as they are in my sample.
I wonder if there is a way to somehow find and extract specifically six-digit numbers. I don't know much about RegEx but I hear that could work.