Dynamic M Parameters with SelectAll List of Values

[rmcconnell340]

I'm working with a Dynamic M Query Parameter in Power Query, where I'm passing vProductKey into a Google BigQuery table function. The single and multi-select filters work perfectly, but I’m running into issues with the "SelectAll" feature.

My bound parameter (vProductKey) is in a bridge table, which is filtered by my product table.

Since Dynamic M Query Parameters have filtering limitations, I need a way to:
1. Filter down the list of product_keys based on selected criteria.
Collect all the filtered product keys.
2. Inject them into my Table Function dynamically.

Where It Breaks
- I have a dashboard where users filter by Department, Category, and an attribute ("Labeled Organic").
- When "Select All" is chosen, my parameter value only contains "SelectAll", instead of a list of all filtered product keys.
- I need a way to capture the filtered product keys from the report and pass them back into Power Query without the end user having to select each product key individually.

Is there a way to dynamically inject a list of filtered product keys into my Power Query parameter when "Select All" is selected?

I’ve attached:
-A snippet of my Power Query code showing how I’m passing vProductKey into BigQuery (yes there is a call to a table column but it doesn't work because it doesn't pull in the filtered table in the report just the full table in PowerQuery)
-A screenshot of my test dashboard, where I’m applying filters and trying to generate the parameter list.

  

[v-shamiliv]

Hi @rmcconnell340 
could you please try this m-query

M-Query:
create a table with all unique values of product keys as Table

Finalstring query:
let
    Source = Table,
    Custom1 = Table.Column(Source,"Product Keys"),
    Custom2 = List.Transform(Custom1, each "'" & _ & "'"),
    Custom3 = "[" & Text.Combine(Custom2,",") & "]"
in
    Custom3

Modification:

// Convert the list of keys into a BigQuery-compatible array format
finalKeysText = If isSelectAll then
// finalstring(from pquery)
          finalstring


 I have attached sample pbix file with sample data for reference.
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community

[rmcconnell340]

@v-shamiliv I am not seeing the Power BI file...could you re-share it?

Also, will this work dynamically? So that if the table of product keys is filtered to a smaller list in the report (say I select all Organic items) and a new list of items is generated in the slicer where I am selecting "Select All" will it just run the list of organic items or all product keys that exist in that table?

Thank you! 

[v-shamiliv]

Hi @rmcconnell340 
As you mentioned, the single select and multi select options are already working. In case you need to select all, you can use the above query. I have attached a sample Pbix file.

Thank you.

[rmcconnell340]

@v-shamiliv is there an example of how to do this? I am not sure how to create this list of values and then re-inject it into my PowerQuery. There is an example below of the M code I am using. I also am providing an additional code where I tried to create logic in the PowerQuery that told it to refer to the list of values from a column if SelectAll was enabled. This gave me a folding error but I also don't know if it would have injected the correct filtered list of items I am trying to create a list of.

If there is a way to make a list of items that I am filtered down to with DAX and then inject it into my SQL expression I am all ears! 

SelectAll

[v-shamiliv]

Hi @rmcconnell340 
Thank you for reaching out microsoft fabric community forum.

Dynamic M Query Parameters in Power BI cannot directly capture the filtered list of ProductKeys when "Select All" is selected. Instead, when "Select All" is chosen, the parameter typically receives a single value like "SelectAll" rather than the actual list of filtered values. To work around this, you need to manually retrieve the filtered product keys using DAX measures or Power Query transformations. This approach ensures that when "Select All" is selected, Power BI still sends the correct list of filtered product keys to the google BigQuery table function. 

If you need any further assistance or have any questions, please feel free to reach  us.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community