Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Extract six-digit numbers from text string

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!

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

     

  • christinepayton's avatar
    christinepayton
    Most Valuable Professional
    2 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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

       

      Thanks for replying! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.