The business now has a reporting requirement that I have to filter the Product Subcategory names by data from another table. I have a Product Subcategory table containing descriptive data of all product subcategories. I’d rather explain the rest with a scenario. There is a function in Power Query that makes it easy, L i st.Contains(list, values). I also mentioned that the results of that referencing would be a List of values right? So what we are after is filtering a column by a list of values. I just mentioned earlier how easily you can reference a column from another table. Our scenario is a bit different though, we want to filter a column by values from another column. So if your reporting requirement changes in the future, you’ll need to redo the filtering and refresh the query. But the query in that case is filtered with constant values. You just need to select the needed values from dropdown and it’s done. Source Filtering a Query Column with Referencing Column from Another Queryįiltering a column using the query editor UI is fairly simple. The result is a list of values of that particular column. So, the format will look like #”QUERY_NAME”.
![3dvia composer bom table highlighting design mode off 3dvia composer bom table highlighting design mode off](http://krisbunda.com/blog/wp-content/uploads/2016/02/Parts-Catalog-OO-Writer-PDF-export-highlightable-searchable-BOMs.png)
When you reference a column you need to mention the referencing query name, explained above, along with the column name in brackets. Referencing a column is also quite simple. So, if I want to reference another query, in a new blank query, then the Power Query (M) scripts would look like below: let If the query name contains special characters like space, then you need to wrap it with number sign and double quotes like #”QUERY_NAME”. It is quite simple, you just need to use the name of the query. So, whenever you refresh your model if new records have been changed in or added to the source of the lookup query, your table will automatically include the new values in the filter step in Power Query.
![3dvia composer bom table highlighting design mode off 3dvia composer bom table highlighting design mode off](https://www.javelin-tech.com/blog/wp-content/uploads/2021/01/Save-Root.jpg)
In that case, you’ll have a sort of dynamic filtering.
![3dvia composer bom table highlighting design mode off 3dvia composer bom table highlighting design mode off](https://i.ytimg.com/vi/GpL76TlbO4Y/maxresdefault.jpg)
This is useful when you have a lookup table that can be sourced from every supported data source in Power Query and you want to filter the results of another query by relevant column in the lookup query.
3dvia composer bom table highlighting design mode off how to#
The key point is to know how to reference a query and how to reference a column of that query in Power Query. In this post I explain how that can be achieved in Power Query. A while ago I was visiting a customer that asked if they can filter a query data by a column from another query in Power BI.