Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
In Power Query, is there a way to dynamically filter (or merge) a primary table based on a column name (and values) found in another table (or list)?
Hopefully this visual helps understand my question:
Main Table (to filter):
RowNumber | Tier1 | Tier2 | Details |
1 | Tier1Val1 | Tier2val1 | blah blah 1 |
2 | Tier1Val2 | Tier2val2 | blah blah 2 |
Filter Table (where I define which column to filter or merge on, and the value to match):
FilterColumn | FilterValue |
Tier2 | Tier2val2 |
Can this be done (without pivoting or unpivoting the first column)?
Thanks in advance!
Solved! Go to Solution.
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
12 | |
11 | |
9 | |
6 | |
6 |