Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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
Super User

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.

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.