cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JWard_CC_work
New Member

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

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):

RowNumberTier1Tier2Details
1Tier1Val1Tier2val1blah blah 1
2Tier1Val2Tier2val2blah blah 2

 

Filter Table (where I define which column to filter or merge on, and the value to match):

FilterColumnFilterValue
Tier2Tier2val2

 

Can this be done (without pivoting or unpivoting the first column)?

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
m_dekorte
Solution Sage
Solution Sage

Hi @JWard_CC_work 

 

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

m_dekorte_0-1683894430532.png

 

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

View solution in original post

BA_Pete
Super User
Super User

Hi @JWard_CC_work ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @JWard_CC_work ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




m_dekorte
Solution Sage
Solution Sage

Hi @JWard_CC_work 

 

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

m_dekorte_0-1683894430532.png

 

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

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors