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

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,095,971
Messages
5,447,619
Members
405,458
Latest member
newbie111

This Week's Hot Topics

Top