Filter a column by calues in a different query

[gjohal]

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!

 

[hossat]

Hi,
it's a nice problem to solve. thank you.

Look at this solution, and tell me if you understand it. Hope it will help you

View attachment Example - solution for gjohal.xlsx


To learn more, visit my personal blog http://numidiabi.wordpress.com

 

[gjohal]

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?

 

[horseyride]

See attached, with file stolen from hossat

function to pull list of items for a variable color

(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"

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

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

 

[p45cal]

Can also be done with a one liner, eg.:


see attached: