Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |