Extracting values in a specific format from a string of text

[Anonymous]

Hi, I am trying to extract a specific code from a string of text.

In the "Sample Data Column" the format of the code is always "00-0000" however I have had no luck with the extract and split functions as the string varies too much and sometimes there is more than one code.

See example below:

Additionally if there are multiple codes in one string I'd like them in one column seperated with a semi-colon and then add a prefix based on another column. The desired result is above.

I've also attached a PBIX in dropbox that contains the sample data.

Any pointers would be appreciated

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous 

According to you description，I think you can first extract the number and “-” character, and then trim it. The specific M language is as follows：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY+xDoQwDEN/xepMkNNyn3EbW8VYiaVCAob7/AutRGFAYoueYzuJ0W1LTtjTb4f6MEApVBIF52Wtmpu66A7Jk3cFgpYwpzWV1YbaFCjm5sUaegWtVYivwX40KGfGuzr4EqsXrLXo8RBz6OMhZg4fX0Wpr09/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sample Column Data" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Select([Sample Column Data],{" ".."9"})),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Sample Column Data"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","  "," ",Replacer.ReplaceText,{"Value"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","  "," ",Replacer.ReplaceText,{"Value"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
    #"Added Conditional Column" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each if Text.Contains([Value.1], "0-") then [Value.1] else null),
    #"Replaced Value2" = Table.ReplaceValue(#"Added Conditional Column",null,"0",Replacer.ReplaceValue,{"Value.3", "Value.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value2",{"Value.5"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Removed Columns",null,"",Replacer.ReplaceValue,{"Value.2"}),
    #"Trimmed Text1" = Table.TransformColumns(#"Replaced Value3",{{"Value.2", Text.Trim, type text}}),
    #"Added Conditional Column1" = Table.AddColumn(#"Trimmed Text1", "Custom.1", each if Text.Contains([Value.2], "0-") then [Value.2] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if Text.Contains([Value.3], "0-") then [Value.3] else null),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each if Text.Contains([Value.4], "0-") then [Value.4] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column3",{"Attribute", "Value.1", "Value.2", "Value.3", "Value.4"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns1",{"Custom", "Custom.1", "Custom.2", "Custom.3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Added Prefix" = Table.TransformColumns(#"Merged Columns", {{"Merged", each ";" & _, type text}}),
    #"Replaced Value4" = Table.ReplaceValue(#"Added Prefix",";;",";",Replacer.ReplaceText,{"Merged"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4",";",";ABC",Replacer.ReplaceText,{"Merged"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Replaced Value5", {{"Merged", each Text.AfterDelimiter(_, ";"), type text}})
in
    #"Extracted Text After Delimiter"

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.