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

[mrchonginhk]

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

 

[theBardd]

This should get you going

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"

 

[mrchonginhk]

Thanks a lot.