Filter a column by calues in a different query

G

gjohal

New member
Joined
Nov 8, 2018
Messages
15
Reaction score
0
Points
0
Excel Version(s)
2016
Hello!

I want to filter a column in my pq by including only values from a specific column in another query. I have attached the file which shows a simplified version of my issue. View attachment Example.xlsx

I want to filter the 3 queries in the group "PQ" by the values in the Filter query. So for the Red query I want only values that are in the Red column in the Filter query.

Can anyone help? I feel like I knew how to do this but I can't remember anymore.

Thanks!
 
Hey,

Thanks for the reply. I understand the solution but I was wondering if this could be achieved by a custom function instead of merging the data?
 
See attached, with file stolen from hossat

function to pull list of items for a variable color

Code: 
(Color as text ) as list =>
let Source = #"Lookup",
#"Filtered Rows" = Table.SelectRows(Source,each ([Column1] = Color)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}),
List = Table.ToList(#"Removed Columns1")
in List

and call to function using color "Blue"

Code: 
Filtered = Table.SelectRows(Source, each List.Contains(Filter("Blue"), [Indicator]))


If you didn't want to hard code the color, enter the color in a named range, here "Bob", and then change code to be

Code: 
 Filtered = Table.SelectRows(Source, each List.Contains(Filter(Excel.CurrentWorkbook(){[Name="Bob"]}[Content]{0}[Column1]), [Indicator]))
 

Attachments

  • Example - solution for gjohal.revised.xlsx
    19.8 KB · Views: 21
Last edited:
Can also be done with a one liner, eg.:
2020-06-30_142154.png

see attached:
 

Attachments

  • ExcelGuru9703Example.xlsx
    17.6 KB · Views: 10
You must log in or register to reply here.
Back
Top