Forum Discussion

MRZ's avatar
MRZ
Frequent Visitor
10 years ago
Solved

parameters to SP or Sql query

There is probably a simple answer but after looking all over for an answer and trying various methods, the right code still escapes me.  The scenario is I have a stored procedure that uses two input ...
  • MRZ's avatar
    MRZ
    10 years ago

    Thank you so much. Will give this a go as soon as possible.

  • MRZ's avatar
    MRZ
    10 years ago

    By the way, this work fine. Thanks so much. Now I need to find out how to update the parameters in PowerBI.com so that i can refresh the data as needed. Thanks again.

KarenL7's avatar
KarenL7
Advocate V
6 years ago

Hi 

Using this query I have been able to manage building a parameter to dynamically use my Stored Procedure Parameters just like I use in my SSRS reports.

 

However, I have an extra Text Parameter so when Invoke the parameter I get an error

"1 arguments were passed to function which expects between 2 and 3 power bi parameter function"

 

This is the code which I amended from above - No syntax errors were found

 

let

    SQLSource = (Title as text, StartDate as date, EndDate as date) =>

 let  

    Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [Schema].[StoredProcedureName] '

    "& Text.Contains(Title) & "','" & Date.ToText(StartDate)&"''" & Date.ToText(EndDate)&"' #(lf)#(lf)#(lf) #(lf)"])

 in

     Source

 in

     SQLSource

 

The first parameter can store a list of text fields as set up in the stored procedure - SSRS parameters can handle these presumably Power BI parameters can too - perhaps I am using the wrong text fields?

 

Any help would be great

 

Thanks

 

Karen