cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taren
Regular Visitor

Filter list of of strings from a column with multiple strings

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

1 ACCEPTED SOLUTION
HotChilli
Super User II
Super User II

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.

 

View solution in original post

2 REPLIES 2
HotChilli
Super User II
Super User II

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.

 

View solution in original post

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

Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors