Extracting substrings from free text

[rovers]

Hi,

Seeking help to code the following in Power Query (preferred) or DAX.

Input Table:

ID; Free Text

Order123; PN113 and PN224 were used to fulfil the order. However, there was no stock of PN113. PN224 was ok.

Order124; There was an issue. PN445 was not found. To resolved this, PN556 was used in place of PN445.

Output Table:

Order123; PN113

Order123; PN224

Order124; PN445

Order124; PN556

The objective is to search for "PN" and list the full PN that appears in the free text. Each PN is standardized to always have 5 characters. 

[lbendlin · Accepted solution]

Here is a crude way of doing this. It assumes that the string "PN"  does not appear anywhere else in the text. So "The driver was hypnotized"  wouldn't work.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY47DoMwEESvMqK2kACTM6QiFHSIwoK1QFjeyB+4fmyS0O7OvDfjWLzcQq6qm0IUfVdVDZRd0Hd1LXGSI0RPCwJDR6M3g7ASOFdKPPmkg5zItxQ8lYdl+MDzDta4aOUflZ68l8UkbqNMxuFuKovN+0i5IGX7owVojnYpMTAceTZHHrNuXqRY2z6u2LVws3gbNdPXnAjJNX0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Free Text" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"."," ",Replacer.ReplaceText,{"Free Text"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Custom.1", each List.Distinct(List.FindText(Text.Split([Free Text]," "),"PN"))),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1")
in
    #"Expanded Custom.1"

[rovers]

This works, thanks a lot. 

However, there were some entries that needed cleaning e.g. (PN789) where the brackets should be removed. I added two more lines of code to clean them.

#"Added Custom.2" = Table.AddColumn(#"Expanded Custom.1", "Custom.2", each Text.PositionOf([Custom.1],"P")),
#"Added Custom.3" = Table.AddColumn(#"Added Custom.2", "Custom.3", each Text.Middle([Custom.1], [Custom.2], 6))

[lbendlin]

I would recommend you add two more replacers instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY47DoMwEESvMnKVSJYlwOQMqQgFHaJAsAiE5Y38gevHkIR2d+a9aVvxciO5LC+EFHWVZQV6O6Ku8lxjJ0eInkYExhTNtBiEmcBHReHJO23k5HFLwb33sAwfeFjBE06a+qPSk1clOnkZdTI2V7O3WLyPpHCrK63L+48XMHG0o0LDcOTZbMecefEykcvyccbOjYvF2/QDfd0JkWzdBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Free Text" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"."," ",Replacer.ReplaceText,{"Free Text"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","("," ",Replacer.ReplaceText,{"Free Text"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",")"," ",Replacer.ReplaceText,{"Free Text"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value2", "Custom.1", each List.Distinct(List.FindText(Text.Split([Free Text]," "),"PN"))),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1")
in
    #"Expanded Custom.1"

[rovers]

Actually (PN789) was just an example. The additional characters could come in other forms such as -PN789. In anyway I used "PN" as a mockup, my real use case is a 3 letter code which is not commonly used in english words. 

[lbendlin]

Fair enough. I seem to recall that there are a couple of more elegant fuzzy replacer functions available - maybe something to consider. Or simply blank out everything that is not a letter or a number.