Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
You should be able to use List.Contains to filter the table.
Consider the example data...
itemTable
criteriaTable
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.
Proud to be a Super User! | |
You need to replace 'PREVIOUSSTEP' with the actual previous step in your table. It may look something like #"Changed Type".
Proud to be a Super User! | |
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'.
You should be able to use List.Contains to filter the table.
Consider the example data...
itemTable
criteriaTable
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.
Proud to be a Super User! | |
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.