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

Re: parameters to SP or Sql query

let
SQLSource = (param1 as text) =>
let
Source = Sql.Database("gx-zwesqld038.database.windows.net", "ITXTestInterikea2", [Query="EXEC [ITX].[ExcludedTransactionsReportByTransactionCount] '"& (param1) & "' #(lf)#(lf)"])
in
Source
in
SQLSource

 

This will Work 

 

 

 

Regards,

Firoj Shaikh

firojshaikh03@gmail.com

Highlighted
New Member

Re: parameters to SP or Sql query

let
SQLSource = (param1 as text) =>
let
Source = Sql.Database("gx-zwesqld038.database.windows.net", "ITXTestInterikea2", [Query="EXEC [ITX].[ExcludedTransactionsReportByTransactionCount] '"& (param1) & "' #(lf)#(lf)"])
in
Source
in
SQLSource

 

 

 

This should work 

Regards,

Firoj Shaikh.

Firojshaikh03@gmail.com

Highlighted
New Member

Re: parameters to SP or Sql query

let
SQLSource = (param1 as text) =>
let
Source = Sql.Database("gx-zwesqld038.database.windows.net", "ITXTestInterikea2", [Query="EXEC [ITX].[ExcludedTransactionsReportByTransactionCount] '"& (param1) & "' #(lf)#(lf)"])
in
Source
in
SQLSource

 

 

check i tried this and it works

still looking out for way to pass invoked function as input . Slicer could be an option but it takes Stored values than random and it inputs as dropdown or list.

 

Regards,

Firoj 

firojshaikh03@gmail.com

 

Highlighted
Regular Visitor

Re: parameters to SP or Sql query

I receive this error after made your step to create a parameters

DataSource.Error: Microsoft SQL: Error converting data type varchar to date.
Details:
DataSourceKind=SQL
DataSourcePath=10.58.211.97,49461;PPL_KPI
Message=Error converting data type varchar to date.
Number=8114
Class=16

Any idea to fix this?

 

Highlighted
Regular Visitor

Re: parameters to SP or Sql query

I received this error mesage after run your steps with my own procedure

 

DataSource.Error: Microsoft SQL: Error converting data type varchar to date.
Details:
DataSourceKind=SQL
DataSourcePath=10.58.211.97,49461;PPL_KPI
Message=Error converting data type varchar to date.
Number=8114
Class=16

 

something Imade wrong?

 

let
SQLSource = (Param1 as date, Param2 as date) =>
let
Source = Sql.Database("10.58.211.97,49461", "PPL_KPI", [Query="EXEC [PPL_KPI].[dbo].[Completed_kpi]'"& Date.ToText(Param1) & "','" & Date.ToText(Param2)&"' #(lf)#(lf)#(lf) #(lf)"])
in
Source
in
SQLSource

 

Highlighted
Regular Visitor

Re: parameters to SP or Sql query

I received this error mesage after run your steps with my own procedure

 

DataSource.Error: Microsoft SQL: Error converting data type varchar to date.
Details:
DataSourceKind=SQL
DataSourcePath=10.58.211.97,49461;PPL_KPI
Message=Error converting data type varchar to date.
Number=8114
Class=16

 

something Imade wrong?

 

let
SQLSource = (Param1 as date, Param2 as date) =>
let
Source = Sql.Database("10.58.211.97,49461", "PPL_KPI", [Query="EXEC [PPL_KPI].[dbo].[Completed_kpi]'"& Date.ToText(Param1) & "','" & Date.ToText(Param2)&"' #(lf)#(lf)#(lf) #(lf)"])
in
Source
in
SQLSource

 

Highlighted
Frequent Visitor

Re: parameters to SP or Sql query

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors