Forum Discussion

user_2020's avatar
user_2020
Copper Contributor
Jul 11, 2020
Solved

PowerQuery -How to apply a parameter to all tabs within a spreadsheet

I am using excel 2016. I created 8 separate tabs in a spreadsheet, each tabs connects to a different table on MS SQL server(ODBC). I apply the exactly same filter (with same inventory number and sales date) in each power query, but everytime when I need to update this filter/parameter, I need to update it one by one for each of these 8 tabs.
Is there a way to create a prompt or global parameter, which means I only need to update the filter once and this could apply to all 8 tabs?
Thanks.
  • user_2020 

    One approach to using a list of values as a filter is to create a filter table and use Merge with an inner join to filter the queries.

     

    I created a filter table on a separate sheet in my workbook and put two countries in there:

     

    Now I select the PopulationStatistics query and use Home>Merge Queries and configure it like this:

     

    Because I'm using an inner join, this has the effect of removing rows from the PopulationStatistics table that don't exist in the CountryFilter query.

     

    This will add a column to the right of the PopulationStatistics query called CountryFilter, but we don't need that, so just right-click it and select Remove.

     

    I then repeat the whole merge process for the LandUse query (or whatever other query has country in it), then use Home>Close&Load.

     

    When the Close&Load has finished, you can delete the additional sheet created for the filter query so that you just have one sheet for each of your original queries and one sheet for your filter table and the list of queries will show "Connection only" against the filter:

    Now, when you change the rows in the filter table (add/remove/edit), you can refresh all queries to re-filter them.

     

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    user_2020 

    Working with Excel I'd suggest to keep parameters in Excel Table or named range(s). Query it, parse on separate parameters and combine them with SQL query string. Implementation is slightly different depends on your privacy setting is Ignore or not.

     

    From end user point of view that is more suitable way to change parameters.

  • OwenPrice's avatar
    OwenPrice
    Iron Contributor

    user_2020 

    To demonstrate how to use a parameter in the way you described, I created two queries against two separate lists of countries from Wikipedia:

     

    1. https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population 

    After filtering away the duplicated column headers, I have this:

     

    2. https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population_density 

    Similarly:

     

    I have also created a parameter using Home>Parameters>Manage Parameters>New Parameter:

     

    In my example, I want to filter both lists by the same country.

     

    I've configured the New Parameter like this:

     

    Now, I can filter each query on the column that contains the country name.

     

    From the filter drop-down in the PowerQuery Editor, I select Text Filters>Equals, then I configure the Filter Rows dialog to be equal to the value of my parameter:

     

    Note that I have changed the second box on the first row to Parameter and the SelectedCountry parameter was selected automatically. If you have more than one parameter, you may need to select this yourself.

     

    I repeat this step for each query containing a country column, then Close&Load back to the workbook.

     

    Now, when I want to change the parameter, I simply change the value of the parameter once and use Refresh All to re-filter all the queries by the new country.

     

    It should be simple to imagine and implement how this will work for multiple parameter-filters.

    • user_2020's avatar
      user_2020
      Copper Contributor

      Thanks, This is a great Idea. I am trying to create a list of Inventory Number as a parameter. However, it seems I could only choose one inventory number at a time. Does that mean I need to create a parameter for each Inventory Number? Or if there is a way to create a list of Inventory Number at once?

       
       
       

       

       

      OwenPrice 

      • OwenPrice's avatar
        OwenPrice
        Iron Contributor

        user_2020 

        One approach to using a list of values as a filter is to create a filter table and use Merge with an inner join to filter the queries.

         

        I created a filter table on a separate sheet in my workbook and put two countries in there:

         

        Now I select the PopulationStatistics query and use Home>Merge Queries and configure it like this:

         

        Because I'm using an inner join, this has the effect of removing rows from the PopulationStatistics table that don't exist in the CountryFilter query.

         

        This will add a column to the right of the PopulationStatistics query called CountryFilter, but we don't need that, so just right-click it and select Remove.

         

        I then repeat the whole merge process for the LandUse query (or whatever other query has country in it), then use Home>Close&Load.

         

        When the Close&Load has finished, you can delete the additional sheet created for the filter query so that you just have one sheet for each of your original queries and one sheet for your filter table and the list of queries will show "Connection only" against the filter:

        Now, when you change the rows in the filter table (add/remove/edit), you can refresh all queries to re-filter them.

         

  • Hi user_2020 

     

    can you please recreate the sample and attached the Excel file 

     

    Manually feed you comments in workbook to understand your query & provide you the best solution 

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

Resources