Forum Discussion
How to filter a column from items in another dataset column
I have two datasets and I want to filter a column in the first dataset against matching items in a column in the second dataset they are both string columns. I'm trying to build a visual that will show all the items in the dataset 1 column that do not contain items from dataset 2 column.
You should be able to use List.Contains to filter the table.
Consider the example data...
itemTablecriteriaTable
you can select rows from itemTable that are not in the criteriaTable adding this line to itemTable
= Table.SelectRows(PREVIOUSSTEP, each not List.Contains(Table.ToList(crtiteriaTable), [Item]))
if you wanted to select rows that matched the criteria, you would remove the 'not' from the above code.
Hope this gets you pointed in the right direction.
4 Replies
jgeddesSuper User
You should be able to use List.Contains to filter the table.
Consider the example data...
itemTablecriteriaTable
you can select rows from itemTable that are not in the criteriaTable adding this line to itemTable
= Table.SelectRows(PREVIOUSSTEP, each not List.Contains(Table.ToList(crtiteriaTable), [Item]))
if you wanted to select rows that matched the criteria, you would remove the 'not' from the above code.
Hope this gets you pointed in the right direction.
I'm running into an error inputting the code into the table the "PREVIOUSSTEP" portion in the code is not a selectable option an errors when I input it manually.
- Jonvoge
Most Valuable Professional
Hi DJH1
You may also consider using a Merge action.
Perform a Left Join, and filter out the subsequent rows where columns from Dataset 2 are empty, meaning that no corresponding item was found.
_____________________________________________________
I hope my comment was helpful.
If your question was answered, please mark your post as 'Solved' and consider giving me a 'Thumbs Up'. - jgeddes
You need to replace 'PREVIOUSSTEP' with the actual previous step in your table. It may look something like #"Changed Type".
