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.

  

[Anonymous]

Hi @rmcconnell340 

We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the support ticket with Microsoft?

If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Thank you for your understanding and participation.

[Anonymous]

Hi @rmcconnell340 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[rmcconnell340]

Hi @Anonymous ,

This unfortunately did not solve the issue. I kept getting folding errors or the configuration was "too complex". I am working on looking for an answer using the embedded report wrapper. Our team is looking to select all the values in the parameter slicer automatically using embedded code.

[Anonymous · Accepted solution]

Hi @rmcconnell340 
If the issue is still unresolved, we recommend raising a support ticket.To create a support ticket for Fabric and Power BI, please refer to the steps outlined in the following guide:

How to create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn

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

[Anonymous]

Hi @rmcconnell340 
can you please try this?

Explainlet
    // Determine if vProductKey is a list or a single value
    isList = Type.Is(Value.Type(vProductKey), List.Type),

    // Check if "SelectAll" is selected
    isSelectAll = 
        if isList then 
            List.Contains(vProductKey, "__SelectAll__")
        else 
            (vProductKey = "__SelectAll__"),

    // If "SelectAll" is chosen, use all values from distinctUPC
    finalKeys = 
        if isSelectAll then 
            Fullstring
        else if isList then 
            vProductKey
        else 
            { vProductKey },

    // Convert the list into a properly formatted BigQuery array
    finalKeysText = Text.Combine(List.Transform(finalKeys, each """" & Text.From(_) & """"), ", "),

    // Construct SQL query with UNNEST function
    vQuery = 
        "SELECT DISTINCT * FROM `mytable`([" 
            & finalKeysText 
            & "])",

    // Execute the query using Value.NativeQuery against BigQuery
    Source = Value.NativeQuery(
        GoogleBigQuery.Database([BillingProject = "spins-retail-solutions"]) 
            {[Name = "spins-retail-solutions"]}[Data],
        vQuery,
        null,
        [EnableFolding = false]
    )
in
    Source

step1:

let
    Source = RenamedTable,
    Custom1= Table.Column(Source, "Product Keys"),
    Custom2= List.Transform(Custom1, each """" & _ & """"),
    FinalKeysText = Text.Combine(Custom2, ", "),
    UnnestQueryText = "UNNEST([" & FinalKeysText & "])"
in
    UnnestQueryText

step2:

vquery = "
        SELECT DISTINCT *
        FROM `mytable`
        WHERE ProductKey IN " & UnnestQueryText & "
    "

step3:

Source = Value.NativeQuery(
        GoogleBigQuery.Database([BillingProject = "spins-retail-solutions"]) 
            {[Name = "spins-retail-solutions"]}[Data],
        vquery,
        null,
        [EnableFolding = false])

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]

@Anonymous I am a little confused with this solution. Are Steps 2 & 3 a different Query or is Step 3 supposed to be the result if SelectAll is selected? If Steps 2 & 3 are a different query do I have to write something to let Power BI know to use the first query when SelectAll is not enabled and Steps 2 & 3 when Select All is enabled?

If I change [EnableFolding = true]) to [EnableFolding = false]) then it will error out on me and tell me that this is not supported by DirectQuery.

I am also getting an error based on the size of the list of values this generates, so the solution may not work either unfortunately. 

[PHarish]

Hi @rmcconnell340 ,
Create two text variables one for when its select all and other is for when there is a single or multi selection.

below string for when condition selectall is met, use the table with distinct product key column for all, you can modify this code based on your select all, select single or selectmultiple using if then to return the productkeys

selectall:
let
    Source = RenamedTable,
    Custom1= Table.Column(Source, "ProductKeys"),
    Custom2= List.Transform(Custom1, each """" & _ & """"),
    FinalKeysText = Text.Combine(Custom2, ", "),
    QueryText = [" & FinalKeysText & "]
in
    QueryText

Keep this as a seperate query for now and pass it to the productkey parameter in below code.

Use the following for your connector code
let
source = GoogleBigQuery.Database([BillingProject = "spins-retail-solutions"]) ,
db=Source{[Name="spins-retail-solutions"]}[Data],
NativeQuery= Value.NativeQuery(db, "SELECT DISTINCT *
        FROM mytable
        WHERE ProductKey IN ( SELECT ProductKey from UNNEST(JSON_QUERY_ARRAY(@productkey)))",
       [productkey = QueryText],
      [EnableFolding = True])
in
NativeQuery

I hope this helps 🙂

[Anonymous]

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]

@Anonymous 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! 

[Anonymous]

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]

@Anonymous I tried you solution in the following code and received an error. I also noticed you are concatenating all the values in DAX formula. Is there a way to load the values from the DAX measure into a Google BigQuery Table Function? Otherwise I don't have a way to take the list of "all selected filtered values" and plugging them back into the TF? 

Query:

let
    // Determine if vProductKey is a list or a single value
    isList = Type.Is(Value.Type(vProductKey), List.Type),

    // Check if "SelectAll" is selected
    isSelectAll = 
        if isList then 
            List.Contains(vProductKey, "__SelectAll__")
        else 
            (vProductKey = "__SelectAll__"),

    // If "SelectAll" is chosen, use all values from distinctUPC
    finalKeys = 
        if isSelectAll then 
            Fullstring
        else if isList then 
            vProductKey
        else 
            { vProductKey },

    // Convert the list into a properly formatted BigQuery array
    finalKeysText = Text.Combine(List.Transform(finalKeys, each """" & Text.From(_) & """"), ", "),

    // Construct SQL query with UNNEST function
    vQuery = 
        "SELECT DISTINCT * FROM `mytable`([" 
            & finalKeysText 
            & "])",

    // Execute the query using Value.NativeQuery against BigQuery
    Source = Value.NativeQuery(
        GoogleBigQuery.Database([BillingProject = "spins-retail-solutions"]) 
            {[Name = "spins-retail-solutions"]}[Data],
        vQuery,
        null,
        [EnableFolding = true]
    )
in
    Source

[rmcconnell340]

@Anonymous 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

[Anonymous]

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