Dynamic Filter by a Range of Values in Power Query

hi you're watching the pad goodly once again and in this video i'm going to talk about how do you apply a range of values as a filter to your data set in power query and that too dynamically let's start all right i'm in this very simple excel file which contains two columnar data we have the category and we have the value and maybe on the category column i'd like to apply a filter but that is not going to be a constant filter for one particular value like filter for a or filter for b i'd like to apply a range of values as a filter and all the values that i'd like to filter have been mentioned out in a separate table here this table is going to be controlled by the user and the user can possibly increase the number of filters or add more values here and he could also reduce it let's just see that if we have these filters which is a range of values how could we dynamically apply it to the category column in the data set so we'll start off by loading the data set into power query and take it from there all right i'm in power query and this is where i have loaded both the data sets into power query you can see that we have the filter table just one column table contains two values a and c and we also have the data which contains two columns on which we have to apply the filter on the category column now i would not be able to apply a entire table as a filter to the category column and hence i need to convert this table into a list what i'm going to do is i'm going to right click on this particular column and i'm just going to use the function called drilldown and this actually converts it into a list if you do not understand what a list means in power query it simply means a one columnar data set now once this has been converted into a list i will take this list and apply it as a filter to the data table and the category column right here the first thing that i will do is i will check that if a manual filter is applied to this particular column what is the kind of m code that is generated and i will tweak that code to fit my filtered list right here so i'm just going to come to this particular column and i'm just going to say hey uh let's just apply a filter b and c and just say ok and what is the kind of m code that we get here so we get table dot select rows and we get the name of the table on which we are trying to apply the filter and the column which is the category column filter b and category column filter c now these two filters b and c are hard coded and they will not change automatically what i need to do is i need to cancel these two filters and i need to write my own filter here so that the filter becomes dynamic and also linked with the list that i just created right here i'm going to use the function called list dot contains list dot contains actually asks you for two inputs the first input is what is the list so my list is certainly filter the list that i just created and the second part of list dot contains is the value that you're trying to search in that particular list the value that i'm trying to search in that particular list that i just created is the value of the category column just check if the value is equals to a or not just check if the value is equal to c or not so on and so forth i will just mention the name of my column right here which is nothing but category now list dot contains actually is going to give you a true or a false as an output and if this is equals to true that is the filter that i'd like to apply i'm just going to close the bracket commit to the formula and press enter you can see that we have successfully applied a and c as the filter to this particular table which is nothing but the two values of this particular list a and c now this could be a filter that you could possibly apply or you could have also tried to apply a conditional filter which is where i don't really want to shorten my list by the list of values but maybe i'd like to create another column and take an action based on the list of values that i have in this particular table if this is good enough sure enough you can work with this i'm going to do the next step which is where i'll take an action based on the two values that i have it in this particular list so i'm just going to come to the data tab and just uh delete this particular filtered rows right here and maybe in the add columns tab i'm just going to create a new column right here i'm going to use the same function and i'm just going to call this column as best and i'm just going to use the function called list dot contains once again so list dot contains now listed contains again has two parts it has the first part as the filter and the second part as the name of the column again or the value that i'm trying to check as a filter this again is going to give me a true and false but this if it gives me a true in that case what i'd like to do is i'd like to take an action and that action can be done using a simple if statement if this formula returns me a true in that case i would like to call this as best product otherwise i'd like to call this as an average product all right simple if then and else i'm just gonna click on ok and all the products that were listed in this particular filter which is anc have been marked as best products and the rest of the products have been marked as average products now let's just load this data to excel and let's just try to tweak our list and see if this is actually dynamic or not all right we're in excel and i have loaded that power query's work into my excel and list here is where we have the list of the values so let's just say that i make two changes the first change is that i replace category c with category d and i say that i also would like to add category b right here now we have three categories and one of the categories change as well and all the products that fall in a d and b would like to be marked as best products right click and i do a refresh and sure enough all of these products are marked as best products all right that was all about how do you apply a range as a filter to your data set in power query you can either apply a range as a filter and then just filter the data or you can apply the range as a filter and take actions based on that if you have any questions around this please feel free to put them down in the comment and i'll be glad to reply in the end a quick shout about my dax and my power query courses if you're starting out and you need help with tax or with power query to learn the fundamentals first and then move on to solving more challenging and more sophisticated problems of your own data i would highly recommend that you take a look at my course it's going to be highly beneficial thanks so much for watching this and i'll catch you guys in the next one bye [Music] you

Channel: Goodly

Views: 43,955

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: eB-LBLS-BRg

Length: 6min 1sec (361 seconds)

Published: Fri Mar 12 2021