Forum Discussion
Filter data based on list from another worksheet
Hi
I have a worksheet "A" which contains extracted data using Power Query. The data has 5000 lines which I want to filter down to just 60 lines using data extracted from another worksheet "B".
A columns are CODE, Name, then more columns
B has only 1 column CODE listing the 60 values. Below is the code to extract the list ...
= Table.SelectRows(#"Removed Other Columns", each ([CODE] <> "" and [CODE] <> "Total"))
Your guidance much appreciated
Hi,
you can try this 2 options
- with merge (RightOuter)
- with List.Contains
adding a custom column
= Table.SelectRows( Table.AddColumn(Source, "Custom", each List.Contains(B[Code], _[Code])), each [Custom]=true)
You can see it in the attached file
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
3 Replies
Hi,
you can try this 2 options
- with merge (RightOuter)
- with List.Contains
adding a custom column
= Table.SelectRows( Table.AddColumn(Source, "Custom", each List.Contains(B[Code], _[Code])), each [Custom]=true)
You can see it in the attached file
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
hi Moomins ,
try using List.ContainsAny after "Each"
Alternatively, would performing and inner join to filter out the relevant data be an option for your (less performant)
if none of this work, kindly provide a sample input and output dataset masking sensitive information
