Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Create a list then pass as parameters to TSQL

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

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi there Anonymous

      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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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