Forum Discussion
Power query. Returning a substring from another column.
Hi guys. I've trying to find a way to separate a part of a text, but I cant find. I have a random sentence that it has a word I want. This word allways have the pattern: letter-letter-number-letter, ...
You can paste this to new blank query and adjust to your query.
2nd step looks for all words of length 4, containing 3 digits with letter on 3rd position.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslIVSgoyk8pTS5RsDA2MzQ1slQwBQKF8sySDIWkxJLkDIXk/JRUBUNjR3OFjMSyVIUSoJbM4uLSVIW80tyk1CIFIxMLM1OlWB3spqEbZGLpSh2DTE3dzahikKGhjylVDDI2C7CkThgZhZpQxSADg0gL/AbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Added Custom" = Table.AddColumn(Source, "Return", each List.Select( Text.Split ( [Text], " "), each Text.Length(_)=4 and Text.Length( Text.Select(_,{"0".."9"}))=3 and List.Contains({"A".."Z"},Text.ToList(_){2}) ) ), #"Expanded Return" = Table.ExpandListColumn(#"Added Custom", "Return") in #"Expanded Return"Result:
Jakinta
Solution Sage
Solution SageYou can paste this to new blank query and adjust to your query.
2nd step looks for all words of length 4, containing 3 digits with letter on 3rd position.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslIVSgoyk8pTS5RsDA2MzQ1slQwBQKF8sySDIWkxJLkDIXk/JRUBUNjR3OFjMSyVIUSoJbM4uLSVIW80tyk1CIFIxMLM1OlWB3spqEbZGLpSh2DTE3dzahikKGhjylVDDI2C7CkThgZhZpQxSADg0gL/AbFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Added Custom" = Table.AddColumn(Source, "Return", each List.Select( Text.Split ( [Text], " "), each Text.Length(_)=4 and Text.Length( Text.Select(_,{"0".."9"}))=3 and List.Contains({"A".."Z"},Text.ToList(_){2}) ) ),
#"Expanded Return" = Table.ExpandListColumn(#"Added Custom", "Return")
in
#"Expanded Return"Result: