How to pass parameter to the source database using odbc connector of redshift

Hi chotu27

You can definitely pass multiple parameter values, just need to think outside of the box a little bit.

For instance, you are not limited to "parameter" as defined by Power BI, you can create a list or a table column of values as well - which you can then convert into a string within the query editor and then pass that string into your SQL query string.

Here is an example for you which you may reproduce:

Step 1: I have created a one column table from "enter data":

Step 2: I have added quotation marks as prefix and suffix, then transposed the table

Step 3 (the mumbo jumbo part): I have converted this to a concatenated string basically. I will not go into details but here is the code for advanced editor:

let
 Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckotSy1KTE8tVorViVZyy89PATPci/KTU4syocKueSWpRSWJmXm5qXklSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Categories = _t]),
 #"Added Prefix" = Table.TransformColumns(Source, {{"Categories", each "'" & _, type text}}),
 #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Categories", each _ & "'", type text}}),
 #"Transposed Table" = Table.Transpose(#"Added Suffix"),

 Headers = Table.ColumnNames(#"Transposed Table"),
 RangedHeaders = List.Range(Headers, 0, List.Count(Headers)),
 ColumnTypesList = List.Generate(
 ()=>[stateList=RangedHeaders, i=0],
 each [i]< List.Count(RangedHeaders),
 each [stateList=[stateList], i=[i]+1],
 each {[stateList]{[i]}, type text}),
 
 #"Changed Type1" = Table.TransformColumnTypes(#"Transposed Table", ColumnTypesList),
 #"Merged Columns" = Table.CombineColumns(#"Changed Type1", RangedHeaders, Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
 Merged = #"Merged Columns"{0}[Merged]
in
 Merged

Which as a result gives me a string like: 'Beverages','Food','Groceries','Entertainment'  which then can be called just like a parameter in your query string.

I named the query input_parameters

Then in ODBC Query string, you can call this as:

let
Source = Odbc.Query("dsn=Google BigQuery", "SELECT line_of_business, category_group FROM masterdata.item_d WHERE line_of_business in (" & (input_parameters) & ")")
in
Source

So if you have a single value you can use actual "parameter", if you have a list of items you want to pass, you can just make a table of it and parametrize it as a list; then pass it into your ODBC query string.

I hope this is helpful to your case.