Results 1 to 3 of 3

Thread: In Powerquery, How to filter a Table using criteria stored in another Table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    663
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default In Powerquery, How to filter a Table using criteria stored in another Table

    Say for example I have a big master table fields:

    Country, Product, Colour, SalesAmount

    I have another table storing the filtering criteria:

    Criteria, Value
    =========
    Country, US
    Product, Keyboard



    I wish to have a resulting PQ output table to filter out US Whiteboard for all colours.
    Any way to do this ?

    Thanks

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: In Powerquery, How to filter a Table using criteria stored in another Table

    This should get you going

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="tblSales"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Product", type text}, {"Colour", type text}, {"SalesAmount", Int64.Type}}),
        Criteria = tblCriteria,
        _country =  Record.Field(Table.SelectRows(Criteria, each ([Criteria] = "Country")){0}, "Value"),
        _product = Record.Field(Table.SelectRows(Criteria, each ([Criteria] = "Product")){0}, "Value"),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Country] = _country) and ([Product] = _product))
    in
        #"Filtered Rows"

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Posts
    663
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: In Powerquery, How to filter a Table using criteria stored in another Table

    Thanks a lot.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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