Tuesday, May 24, 2022
HomeBusiness IntelligenceFast Suggestions: Learn how to Filter a Column by one other Column...

Fast Suggestions: Learn how to Filter a Column by one other Column from a Completely different Question in Energy Question


Filter a Column by a Column from a Different Query in Power Query

Some time in the past I used to be visiting a buyer that requested if they’ll filter a question knowledge by a column from one other question in Energy BI. And I stated in fact you’ll be able to. On this put up I clarify how that may be achieved in Energy Question. The important thing level is to know find out how to reference a question and find out how to reference a column of that question in Energy Question. That is helpful when you might have a lookup desk that may be sourced from each supported knowledge supply in Energy Question and also you need to filter the outcomes of one other question by related column within the lookup question. In that case, you’ll have a form of dynamic filtering. So, everytime you refresh your mannequin if new data have been modified in or added to the supply of the lookup question, your desk will routinely embody the brand new values within the filter step in Energy Question.

Referencing a Question

It’s fairly easy, you simply want to make use of the title of the question. If the question title comprises particular characters like area, then it’s worthwhile to wrap it with quantity signal and double quotes like #”QUERY_NAME”. So, if I need to reference one other question, in a brand new clean question, then the Energy Question (M) scripts would appear like under:

let
    Supply = Product
in
    Supply

Or one thing like

let
    Supply = #"Product Class"
in
    Supply

Referencing a Column

Referencing a column can be fairly easy. Whenever you reference a column it’s worthwhile to point out the referencing question title, defined above, together with the column title in brackets. So, the format will appear like #”QUERY_NAME”[COLUMN_NAME]. The result’s an inventory of values of that specific column.

let
    Supply = #"Product Class"[Product Category Name]
in
    Supply
Referencing a Column from Another Query in Power Query

Filtering a Question Column with Referencing Column from One other Question

Filtering a column utilizing the question editor UI is pretty easy. You simply want to pick the wanted values from dropdown and it’s completed. However the question in that case is filtered with fixed values. So in case your reporting requirement adjustments sooner or later, you’ll must redo the filtering and refresh the question. Our situation is a bit totally different although, we need to filter a column by values from one other column. I simply talked about earlier how simply you’ll be able to reference a column from one other desk. I additionally talked about that the outcomes of that referencing could be a Listing of values proper? So what we’re after is filtering a column by an inventory of values. There’s a operate in Energy Question that makes it simple, List.Incorporates(listing, values).

I’d somewhat clarify the remainder with a situation. I’ve a Product Subcategory desk containing descriptive knowledge of all product subcategories. The enterprise now has a reporting requirement that I’ve to filter the Product Subcategory names by knowledge from one other desk. The second desk comprises solely permitted subcategories. The second desk title is “Product Subcategory Lookup”. The information within the “Product Subcategory Lookup” is incessantly up to date by the enterprise.

The one factor I must do is to do is to make use of the Listing.Incorporates operate like under:

Listing.Incorporates(#"Product Subcategory Lookup"[Approved Subcategory], [Subcategory Name])

For those who’re used to make use of the question editor UI then you’ll be able to simply apply a filter to the [Subcategory Name], then change the code as under:

Power Query, Filter Column with Another Column Values from a Different Query

For those who’re extra hands-on and like writing the M codes then use the Superior Editor to sort the codes.

#"Filtered Rows" = Desk.SelectRows(#"PREVIOUS_STEP", every Listing.Incorporates(#"REFERENCED_TABLE"[REFERENCED_COLUMN], [COLUMN_TO_BE_FILTERED]))

For these of you who’re extra acquainted with SQL, the above M code works just like the under SQL script (in case your supply is SQL Server):

SELECT productsubcategorykey          
       , productsubcategoryalternatekey 
       , [Subcategory Name]
FROM   DimProductSubcategory
WHERE  [Subcategory Name] IN (SELECT [Approved Subcategory] 
							  FROM [Product Subcategory Lookup])

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments

x