Forum Discussion
Solved
Extracting substrings from free text
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 PN1...
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"
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"
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.
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.