referencing parameters from another worksheet in power query

Question

I have some data under one work sheet say "A" (which is extracted thru some other sheet using power query) and I want to filter the data under it. But I want to put filtering table in other work sheet say "B". So when I select some filter in B and hit refresh in power query for A; I wana see my data filtered based on the criteria that I have selected in B . How can this be achieved?

Answer 1

Load both tables in Power Query and use "merge queries" to join them with an inner join (in a new query). In the code below I filter TableA on field "SomeText" based on column "Filter" in TableB.

let
    Source = Table.NestedJoin(TableA,{"SomeText"},TableB,{"Filter"},"NewColumn",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Source,{"NewColumn"})
in
    #"Removed Columns"