Extract text from column and merge it to a particular format

[josectps]

Hi,

In power query what process should I follow so if I introduce the first column with header ¨Sample¨ I would generate the second column with header ¨Result¨?

The text that will be my ¨Result¨ could be find beginning, middle or end of the text.
The ¨Result¨will be a concatenation of DQA, STP or QA, # and then 4 numerical digits.

Thank you in advance for your help.

[dufoq3]

Hi @josectps, 

for future requests provide sample data as table so we can copy/paste.

Try this:

If you want to check every single step, remove this part of code and expand the record.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY2xCoMwGIRf5YhrK7/GWjqGuhQcKpUu4pDWoKGaSBL6/A2CQ+GWO7676zp2naQZFYJFfqZlgV/lW5sRVSOOGSfWHzr2aO+cMjyVC/otZ7ykw2ztx2O0dtiQRiREJw5R12ma7q2EMp7jFqA9jA1b3E7RRMWDJCeCNj4oOeyVoij/jjZWG8xflBuTXQiNQKVmFRR8cHrF4GScYX3/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sample = _t]),
    Ad_Result = Table.AddColumn(Source, "Result", each 
          //remove characters
        [ a = Text.Remove([Sample], {"#", "-"}), 
          //text to list by space delimiter
          b = Text.Split(a, " "),
          //extract strings containing "DQA", "STP" and "QA"
          c = List.Accumulate({ "DQA", "STP", "QA" }, {}, (s,c)=> s & List.Select(b, each Text.Contains(_, c ) ) ){0}?,
          //combine string with number (reorder text first, number afterwards)
          d = if List.ContainsAny(Text.ToList(c), {"0".."9"}) then c
              else [ d1 = List.PositionOfAny(b, { "DQA", "STP", "QA" }),
                     d2 = if List.Contains(Text.ToList(b{d1+1}?),{"0".."9"}) then c & b{d1+1}?
                          else c & b{d1-1}?
                   ][d2],
          //split text and numbers separately to list         
          e = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains({"0".."9"}, x), {"0".."9"})(d),
          //combine text with # and add 0 as prefix if necessary to match 4 character length number
          f = e{0} & "#" & Text.PadStart(e{1}, 4, "0")
        ][f], type text)
in
    Ad_Result

[josectps · Accepted solution]

This is very helpful. I don't have much experience with Power Query.

Thank you very much guys.

[dufoq3]

So you decided to mark your own reply as solution. 😃

[Joe_Barry]

Hi @josectps 

This might work

You need to change some symbols etc first in the existing column.

• Highlight the Sample Column and click on Replace Values in the Ribbion
• The first one to change is the - symbol replace this with #0
• Repeat and change P3 with P#03
• Repeat P4 with P#04
• Repeat 190 QA  with  QA#0190
• Create a custom column and enter the below code

Text.Split([Sample], " ")

•  Extract the values from the list and replace this part of the text " {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})" with {"Custom", each List.Select(_, each Text.Contains(_, "#")), type list})
• Extract the values agin in the next step.

This should help.

Thanks

Joe