Forum Discussion
Dynamic M Parameters with SelectAll List of Values
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.
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
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.
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 productkeysselectall:
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 🙂
