Extract six-digit numbers from text string

[andrewfinberg]

Hello! I am working in Power Query and have a column called "Comments" that contains one or more six-digit numbers. I need to keep the Comments field, and add a Custom Column that extracts the six-digit numbers into their own field. 

EDIT: "REQUEST" does not always precede the request number(s).

Are there any M Code ninjas able to help? 🙂 

Thanks!

[v-tangjie-msft · Accepted solution]

Hi @andrewfinberg ,

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. 

[v-tangjie-msft]

Hi @andrewfinberg ,

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. 

[andrewfinberg]

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. 

[christinepayton]

If "REQUEST-" always preceeds the numbers, you could split the column on that, then split the result on " submitted." to remove the text from the end. 

If you're trying to use it as a filter or relationship, though, you'll want to split the request numbers "to new rows" on the comma at the end of it all. 

[andrewfinberg]

Unfortunately, "REQUEST" does not always precede the request number(s). I just updated my original post to reflect that.

Thanks for replying!