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

mrchonginhk

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

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"
 

Forum statistics

Threads
1,077,823
Messages
5,336,569
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top