Filter list of of strings from a column with multiple strings

[taren]

I have a column that contains multiple strings separated by a delimiter. The number of strings could be in the range from 0-1000. I wanted to filter the strings based on another list of static strings and remove all the strings from the column which do not match.

Column A

Red;blue;white;grey;black;orange;yellow;pink

white;grey;orange;pink

Blue;Red;Orange;yellow;pink

Static list of values to retain in the column: {white;orange;red;blue}

Expected filter outcome :

Column A

Red;blue;white;orange

white; orange

Blue;Red; Orange

[HotChilli · Accepted solution]

Create a Power Query list with the colours to retain. Let's call it ColourList.

Then in the Query with ColumnA add 2 steps:

Step1 Add a new column "Custom" which turns ColumnA into a list using

Text.Split([Column A],";")

Step2 Add another column which does the work of comparingthe lists using

List.Intersect({ [Custom], ColourList}))

that'll return the filtered list and you can do whatever you want with that.

Please be aware that this will be a case-sensitive comparison of list items.  If you want a case-insensitive comparison I think you can use the extra parameter in List.Intersect.

Have a look a this for help:

https://blog.crossjoin.co.uk/2017/01/22/the-list-m-functions-and-the-equationcriteria-argument/ 

Let me know how it goes.

[Ashish_Mathur]

Hi @HotChilli.  I could not get your solution to work.  Could you kindly share the PBI file with your solution.

Thank you.