Replace if Not Like

[hatstand]

Hi all,

I have a Column in (Full List). I want to replace everything that is not in the (My List) Table, resulting in (After List). I can then use a function to remove any multiple ‘|’ . I was thinking about an 'Is Not Like' and each word to find would be one step. But as I have 120 words to find, it would be very long. I thinking something like a 'Not Like' function but cannot find something to do it..

Any pointers would be much appreciated.

Full List

|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z

|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O

|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R

My list

|A|

|H|

|W|

After List

|A|||||||H|||||||||||||||W|||

|A|||||||H|||||||

|A|||||||H||||||||||

 

[RoryA]

It sounds like you probably want

List.Intersect

to me.

 

[hatstand]

Thanks for the reply. I had looked at that function, but it doesn't do what I need. I'm basically looking for something that says, replace anything within the column that isn't found in My List . Unfortunately PQ doesn't support a 'Not Like' so I'm finding it very frustrating. But I'm sure there is an answer to it.

 

[RoryA]

How does it not do what you need? If you apply it to the list of values in your Full list column and the list of values in My List, it will return only the values in both. Is that not what you want?

 

[hatstand]

You will be right of course as I'm new to PQ. I am now struggling to use List.Intersect. I have tried creating anew Query and using, with a failure. On a side note, I am looking to learn PQ to a good standard, can you recommended or can anyone recommend a good book.

let

#"Added Custom" = Table.AddColumn("Custom", each List.Intersect(Table1[Full List],Table2[My List]))
in
#"Added Custom"

 

[RoryA]

If both of your data sets are not currently Lists, you'll need to convert them to one before you can use that. (it looks like you might need to split your text by a delimiter to get a list for the first one)

I'd recommend the M is for Data Monkey book by Miguel Escobar and Ken Puls as a good starting point.

 

[hatstand]

Much appreciated I will get those books. Thanks.