Hi Mynda,
I tried to apply the technique you demonstrate in "7.03 Custom Function for Dynamic File and Folder Paths (0:09:50)" tutorial to extract data from SQL Server Database.
The reason I need that is to get rid of an old fashioned SSRS report generating of which which is very time consuming and requires a lot of manual manipulations.
I took the stored procedure which is used to generate SSRS report and put it into Power Query SQL Statement field. I successfully got the data set into Power Query!!!))) which made me very excited!!!))))
Then I tried to introduce the dynamic parameter into the stored procedure by using SQL query instead of dynamic folder path as per your example in the 7.03 tutorial.
I created the function fnReporting, then the table "StoredProcReporting" (see "Parameters" tab) and put into the table the M-query with the dynamic parameter (similarly to dynamic folder path) and so on as per your tutorial.
In the end I got this (((((:
Expression.Error: 1 arguments were passed to function which expects between 2 and 3.
Details:
Pattern=
Arguments=List
My M-query is
let
Source = Excel.CurrentWorkbook(){[Name="StoredProcReporting"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StoredProcReporting", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnReporting([StoredProcReporting]))
in
#"Added Custom"
Please, advise me as to how I have to modify the query to make it works.
Hi Oleg,
Why do you need a dynamic path to connect to your SQL server? Usually the server path doesn't change, unlike an Excel workbook.
Ken Puls has written about using dynamic parameters for SQL statements here: http://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/
I hope that points you in the right direction.
Mynda
Hi Oleg,
The problem is in your function, fnReporting:
This custom function is using the Sql.Database function, which has 2 text arguments, and a third optional argument, as described below:
See the function description here: Sql.Database
You will see there the details for the optional record parameter, options, if needed.
The record parameter is specified as [option1 = value1, option2 = value2...] or [Query = "select ..."] for example.
You are sending to this function only one text string:
"hoprddb01.YORKSTREET.LOCALDW", "DW", [Query="exec [usp_RS_ProfitReport] '11, 12, 13, 14, 15, 16, 17, 24, 25','1, 2, 3, 4, 5, 6, 7, 8','Reporting','93',3,'All','-999',null,null"]
The function should be:
let
Source = Sql.Database(Server, Dtb, Options)
in
Source
Hi Mynda and Catalin,
I finally managed to do what I need thanks to your advises!
let
parameter1 = fnGetParameter("StoredProcReporting"),
Source = Sql.Database(dbServerName, dbName, [Query="exec [usp_RS_ProfitReport] '11, 12, 13, 14, 15, 16, 17, 24, 25','1, 2, 3, 4, 5, 6, 7, 8','Reporting'," & parameter1 & ",3,'All','-999',null,null"])
in
Source
Where fnGetParameter() is
(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value
Once again thank you for your help!!!
Great, glad to hear you managed to make it work 🙂
Cheers,
Catalin
