cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pedzilla Member
Member

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Create a list then pass as parameters to TSQL

Hi @Pedzilla,

 

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

 

Parameter:

Capture.PNG

 

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

 

Capture2.PNG

 

Invoke result:

Capture3.PNG

 

Edit the parameter to show the specify records.

 

Capture4.PNGCapture5.PNG

 

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

 

Capture7.PNG

 

Capture6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
Pedzilla Member
Member

Re: Create a list then pass as parameters to TSQL

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

 

Community Support Team
Community Support Team

Re: Create a list then pass as parameters to TSQL

Hi @Pedzilla,

 

>>Is that doable?

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

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 238 members 2,617 guests
Please welcome our newest community members: