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

[chotu27]

Hi All, 

Can anyone tell how to pass the parameter that is already created in power bi to  the source database which ODBC connection with redshift.

My parameter name is studyname

let
Source = Odbc.Query("dsn=Amazon Redshift ODBC DSN" select country , totalnumberofsites,group by StudyName, da.state ,c.studyname#(lf) ) as t#(lf)group by StudyName, t.state#(lf)order by t.state"),
#"Filtered Rows" = Table.SelectRows(Source, each ([studyname] = studyname))
in
#"Filtered Rows"

[mede]

Hi @chotu27

Why don't you try to pass the paremeter into your original ODBC query string (select statement) as a WHERE clause?

Here is an example:

Parameter name: LOB

Parameter value: Shop

Paremeter format: text

(I am using an example from my data)

I would first create the original odbc connection with custom sql statement:

SELECT line_of_business, category_group FROM masterdata.item_d WHERE line_of_business in ('Shop')

In the advanced query editor, replace the Shop piece with your parameter name like the following example:

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

and volia! your parameter is filtering the original sql query

[chotu27]

@mede That is already i treied which is working but thing is that I am only able to get for only one value in parameter it is not filtering with all the values in parameter

[mede]

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.

[vsinha]

let
dtbs = Excel.CurrentWorkbook() {[Name="dtbsname"]}[Content],
Source = Odbc.Query("dsn=srvr", "select top 1000 latitude from [linkedserver]." & dtbs & ".dbo"
in
Source

Expression.Error: We cannot apply operator & to types Text and Table.

Any solution to this? It would be very helpful. Thank you!

[vsinha]

let
dtbs = Excel.CurrentWorkbook() {[Name="dtbsname"]}[Content],
Source = Odbc.Query("dsn=srvr", "select top 1000 latitude from [linkedserver]." & dtbs & ".dbo"
in
Source

Expression.Error: We cannot apply operator & to types Text and Table.

Any solution to this? It would be very helpful. Thank you!

[v-ljerr-msft]

Hi @chotu27,

Based on my experience, there is no option to do it currently, and using Table.SelectRows function to filter the data with parameter is the option we have. 

Regards