Forum Discussion
Helper IIRedshift Direct Query - Using (List) Parameters
Alexis - Thanks so much for your help, but when I tried using your method as a template for my situation it generated a query at the detail level. That is, it tried to import the full seven million row result into memory as part of its execution.
However, I think I did find a solution: by using a fully contructed raw sql string in a native query, I was able to insert the parameter values directly into the sql string itself so that it executed remotely on Redshift and retrieved the pre-aggregated results. So far this works locally in Desktop and in the workspace using a data gateway connection to Redshift.
I know this doesn't map to your example, but here's how my solution works:
let
programList =
if
Type.Is(Value.Type(param_programid), List.Type) then
let
// convert parameter to comma delimited text string with values wrapped in quotes for 'IN' expression
// conversion logic if multiple values (i.e. is a list)
AddSingleQuotes = List.Transform(param_programid, each "'" & _ & "'"),
DelimitedList = Text.Combine(AddSingleQuotes, ",") in DelimitedList
// add single quotes if parameter is a single value
else "'" & param_programid& "'",
Source = Value.NativeQuery(AmazonRedshift.Database("xxxxxxxxxxxxx:5439","dev"),
"select number_of_programs_participated, count(*) as user_count from (select userid, count(distinct programid) as number_of_programs_participated#(lf)from source_data_table #(lf)
where programid in (" & programList & ") #(lf)group by userid) pc #(lf)group by number_of_programs_participated", null, [EnableFolding=true])
in
Source
Super UserI think I have a working example that's analogous to your question and connects the sample database mentioned in this Power BI Community Blog post.
I did all of the grouping logic in the query editor and let it fold that back into a native query to be sent back to the source. The parameter selection is inserted as a where-clause into the native query that gets sent back to the source. Please see attached.
Thanks to Chris Webb for help with the single/multiple value parameter handling:
https://blog.crossjoin.co.uk/2020/11/08/handling-multi-select-in-power-bi-dynamic-m-parameters/
Helper IIAlexis - Thanks so much for your help, but when I tried using your method as a template for my situation it generated a query at the detail level. That is, it tried to import the full seven million row result into memory as part of its execution.
However, I think I did find a solution: by using a fully contructed raw sql string in a native query, I was able to insert the parameter values directly into the sql string itself so that it executed remotely on Redshift and retrieved the pre-aggregated results. So far this works locally in Desktop and in the workspace using a data gateway connection to Redshift.
I know this doesn't map to your example, but here's how my solution works:
let
programList =
if
Type.Is(Value.Type(param_programid), List.Type) then
let
// convert parameter to comma delimited text string with values wrapped in quotes for 'IN' expression
// conversion logic if multiple values (i.e. is a list)
AddSingleQuotes = List.Transform(param_programid, each "'" & _ & "'"),
DelimitedList = Text.Combine(AddSingleQuotes, ",") in DelimitedList
// add single quotes if parameter is a single value
else "'" & param_programid& "'",
Source = Value.NativeQuery(AmazonRedshift.Database("xxxxxxxxxxxxx:5439","dev"),
"select number_of_programs_participated, count(*) as user_count from (select userid, count(distinct programid) as number_of_programs_participated#(lf)from source_data_table #(lf)
where programid in (" & programList & ") #(lf)group by userid) pc #(lf)group by number_of_programs_participated", null, [EnableFolding=true])
in
Source