Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.