Forum Discussion
Not Like function
You can do this in a single step with a custom column.
Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|")Full sample query you paste into the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKslIVVBITixRKE4sUdJRcslPB5K5lQoZ+aXFqUqxOtFQofyy1CKFxLwUBRADyHcGKkfIOoM1+1YqpCcWpaTmgWU88ouAJsDkXPKB6mMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|"), type text) in #"Added Custom"
Unfortunatley I cannot use the actual data due to my work etc, sorry. But I have a created basic tables of what I am trying to achieve. As you can see, the first table is the raw data, the Pets table is a list of pets and the third is the first table with a merged Column with “|” delimiter. The Result Column is what I am trying to achieve, by either looking up the pets and returning the matched word. Or removing all words that are not in the Pets table. Any duplicate "|" can be removed with a function.
I hope that makes more sense.
Sorry, but I am having a few Internet problems, so I am not ignoring the advice. Thanks
- AlexisOlson
Super User
You can do this in a single step with a custom column.
Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|")Full sample query you paste into the Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKslIVVBITixRKE4sUdJRcslPB5K5lQoZ+aXFqUqxOtFQofyy1CKFxLwUBRADyHcGKkfIOoM1+1YqpCcWpaTmgWU88ouAJsDkXPKB6mMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Text.Combine(List.Intersect({Record.ToList(_), Pets[Pets]}), "|"), type text) in #"Added Custom"Wow, that is perfect. It has also made me realise how far away from being competent in PQ I am. I have been recommended a book 'M is for Data Monkey book by Miguel Escobar and Ken Puls'. So I will be getting one of those and if you or anyone else could recommend other good books or web sites I would be more than happy to take your advice.
- AlexisOlson
I've heard good things about that book too. An updated version came out relatively recently, so make sure to get the 2nd edition.
I also recommend this blog series:
https://bengribaudo.com/blog/2017/11/17/4107/power-query-m-primer-part1-introduction-simple-expressions-let