Very specific question on Using OPENROWSET in PBI with a multivalued parameter

[KevinSGoff]

Hi, I've got a very specific question/issue, here are the bare details.

I'm using PBI in the cloud, where I have to call a stored procedure using DirectQuery.  So we had to turn on Distributed AD Hoc   in SQL Server to get it to work.  Here was the syntax in Power Query for the DirectQuery dataset: this was based on a single Parameter value.  This part works fine:

= Sql.Database("SERVERNAME", "DATABASE" ,
   [Query="SELECT * FROM OPENROWSET #(lf)('SQLNCLI','server=SERVER ;uid=ID;pwd=PW',
        'EXEC DATABASE.DBO.RunMyProc  """ & @KeyValue & """')"]

However, I had to turn the proc into a multi-select.   So inside the proc, I used the STRING_SPLIT function in SQL Server, to convert a CSV list into a table variable.   That part works and when I test the proc inside of SQL SSMS, that works fine.

However,  in PBI when I make the dynamic parameter a multi-select (and I've written code to check if the @Keyvalue is a list, I get all sorts of errors, regardless of what I try.

So - given what I have above....if the @KeyValue parameter is now a CSV list, and I know the proc can take that CSV parameter and turn it into a table variable - how would I change the Power Query code above?   I have to believe it's something simple.  I've looked at different Web examples, but they are all slightly different in some way.

If anyone has any thoughts.....really in a bind here.   Thanks!

Kevin S. Goff

[Anonymous]

Hi @KevinSGoff 
Could you please let us know if your issue has been resolved? If so, kindly mark the helpful reply and accept it as the solution. This will assist other community members in resolving similar problems more quickly.
Thank you.

[Anonymous]

Hi @KevinSGoff 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous]

Hi @KevinSGoff 
Thank you for reaching out microsoft fabric community forum.
I wanted to check if you had the opportunity to review the information provided @lbendlin . Please feel free to reach us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[lbendlin]

 Sql.Database("SERVERNAME", "DATABASE" ,
   [Query="SELECT * FROM OPENROWSET #(lf)('SQLNCLI','server=SERVER ;

Why are you connecting to the server twice?  Are you doing server hopping with linked servers?

In Power Query use Value.Is()  to probe the type of the object (list or scalar)  and consider using try ... otherwise ... to harden your code.

[KevinSGoff]

Hi, sorry for the delayed reply.
Thanks for your response (and yes, we need to use linked servers, that is another story, though let me clarify:

See the code below.   This was calling a proc called "RunMyProc " and passing in a single string key parameter.   That worked fine.

I changed the proc to receive a long CSV string for multiple keys - inside the SQL proc, I'd convert the long CSV to a table variable and join on it.

However, I can't find the "magic syntax" that will allow me to pass @KeyValue as a comma-separated list of strings.

@Keyvalue is a parameter coming from a slicer tied to a parameter that allows a mult-select.

So I might have a list of keys that the user selects....out of 1,000, it might be

101
104
105
107

I want to pass that list of four keys (or 8 keys, or whatever) to the proc, using DirectQuery below.
And I'm stuck on the syntax - Maybe because I came from SSRS, I'm used to doing this with SSRS and T-SQL and strings, and I'm stuck. 

Again, here is the syntax I'm trying to modify, and I'm stuck.    Thanks


= Sql.Database("SERVERNAME", "DATABASE" ,
   [Query="SELECT * FROM OPENROWSET #(lf)('SQLNCLI','server=SERVER ;uid=ID;pwd=PW',
        'EXEC DATABASE.DBO.RunMyProc  """ & @KeyValue & """')"]

[lbendlin · Accepted solution]

Not clear where you are stuck?  Do you have problems converting the list into a string?