Create a list then pass as parameters to TSQL

[Anonymous]

Hi there,

A couple of questions

I am trying to create a template.

I want to be able to select "multiple values" from parameters.

Then pass these values to the data source

for example

Select * from table1 where value in (1,2,3)

So replacing 1,2,3 using Parameters in Powerbi

Then replacing that with the p

let

//assuming formid is my parameters and I want it to allow multiple values
formid = {ServiceId}{0},

//Create the query
 dbQuery = "select * from [dbo].[table]  where ServiceFormId in ('" & formid & ")'",

//Get the data
 Source = Sql.Database("db","dbname",[Query=dbQuery])
 in
 Source

That didnt work

Is it what I tried to do isnt doable or did i do it wrong?

Thank you

Helpp

[v-shex-msft]

Hi @Anonymous,

You can use custom function and parameter to achieve your requirement.

Parameter:

function formula:

let
    loadDataBase=(ServerName as text,DataSource as text, parameter as text) as table=>
    let
        SQLQuery = "select * from test1121  where Quarter in ("& parameter &")",
        Source = Sql.Database("ServerName", "DataSource",[Query=SQLQuery])
    in
        Source
in
    loadDataBase

Invoke result:

Edit the parameter to show the specify records.

In addition, you can also use the t-sql query as the parameter.

let
    loadDataBase=(ServerName as text,DataSource as text, SQLQuery as text) as table=>
    let
        Source = Sql.Database("ServerName", "DataSource",[Query=SQLQuery])
    in
        Source
in
    loadDataBase

Regards,

Xiaoxin Sheng

[Anonymous]

Hi there @v-shex-msft

Almost quite there

but instead of typing value (1,2,3) in I would like to pick it from my list

My list got following value

1

2

3

4

5

6

and I want to be able to pick multiple values from the list and reshape it to e.g 1,2,3 etc...

Is that doable?

Many thanks

[v-shex-msft]

Hi @Anonymous,

>>Is that doable?

I think it is impossible. You can only get one value from the parameter which stored in the list.

Regards,

Xiaoxin Sheng