Results 1 to 5 of 5

Thread: Filter a column by calues in a different query

  1. #1
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016

    Lightbulb Filter a column by calues in a different query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. 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!

  2. #2
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    112
    Articles
    0
    Excel Version
    365
    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

    Example - solution for gjohal.xlsx


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

  3. #3
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016
    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?

  4. #4
    Conjurer horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    104
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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]))
    Attached Files Attached Files
    Last edited by horseyride; 2019-01-04 at 09:39 PM.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    Can also be done with a one liner, eg.:
    Click image for larger version. 

Name:	2020-06-30_142154.png 
Views:	10 
Size:	7.4 KB 
ID:	9897

    see attached:
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •