Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |