Dynamic Filter or Query table based on column name in another table (or list)?

Hi Anonymous 

You could use Record.Field within Table.SelectRows, copy this script into a new blank query. 

Note that "Filters" is included in this query but you can just as easily place that in a separate query as long as you give that new query the same name OR update the name in the expression below.

let
    Filters = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMLTJS0oHQZYk5RkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FilterColumn = _t, FilterValue = _t]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrJTC0yDEvMgbGNyiDspJzEDAUwYagUqxOtZISk1ghJrRGKWiOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RowNumber = _t, Tier1 = _t, Tier2 = _t, Details = _t]),
    SelectRows = Table.SelectRows( Source, each Record.Field( _, Filters{0}[FilterColumn] ) = Filters{0}[FilterValue] )
in
    SelectRows

Here's the result

Ps. If this helps solve your query please mark this post as Solution, thanks!

Hi Anonymous ,

I'd buffer your filter table column as a list and pass it into a Table.SelectRows, like this:

Table.SelectRows(
    previousStepName,
    each List.Contains(List.Buffer(FilterTable[FilterValue]), [Tier2])
)

The advantage with this over m_dekorte 's answer, and others, is that this method will fold to the source, even if the second (filter) table isn't on the server.

Power query will buffer the filter table column then stream it to the foldable source in the WHERE clause of the native query, making it clean, tidy, and fast.

Pete