How can I add a where clause in my extend editor to my data base containing a parameter

[obc4758]

I have the following:

   let Source = Sql.Database("Momo", "WorkCollaboration"), dbo_TimeReportOverview = Source{[Schema="dbo",Item="TimeReportOverview"]}[Data], #"Renamed Columns" = Table.RenameColumns(dbo_TimeReportOverview,{{"TimeReportSupContactPointValue", "Point Value"}}) in #"Renamed Columns"

I have created a parameter SupplierContactIdParam  

I would like to say select * from Table TimeReportOverview where SupContactId = SupplierContactIdParam  

In the url to the report I would like to pass the value of the parameter using &rp:SupplierContactIdParam= 1

Tks for helping I'm totally new to this Christof

[obc4758]

I got a bit further. However on Power Query Result data are shown without an error. However when I exit it the power query it raises and error... 

My Query looks now like this

let
Quelle = Sql.Database("Momo", "WorkCollaboration", [Query="DECLARE @para AS Int=1;#(lf)Set @para = 1;#(lf)Select * from dbo.TimeReportOverview where SupContactId = @para"])
in
Quelle

In addition I failed putting in my parameter 

I tried on the "set @para = 1" to use my parameter field with this syntax "set @para = &SupContactIdParam& without success. Could someone help me on the correct syntak please. Help would be very appreciated.

[obc4758]

Dear All one further step after reading a lot...

So I had to create a stored procedure to create the working query

create Procedure SelectPersonalTimeReport @SupContactIdParam int
as exec WorkCollaboration.dbo.SelectPersonalTimeReport @SupContactIdParam = 1
Select * from dbo.TimeReportOverview where SupContactId = @SupContactIdParam;

The I created my report. Unfortuantely it does only work on import an not direct query. However at least ist works.

= Sql.Database("Momo", "WorkCollaboration", [Query="exec WorkCollaboration.dbo.SelectPersonalTimeReport @SupContactIdParam = 1"])

Now I want to replace my value by a parameter value in ordert to publish out of my application the value via url to my report.

Sadly I failed to put in my Parameter. 

I tried three options

a) = Sql.Database("Momo", "WorkCollaboration", [Query="exec WorkCollaboration.dbo.SelectPersonalTimeReport @SupContactIdParam = " & SupContactIdBiParam & "])

b) = Sql.Database("Momo", "WorkCollaboration", [Query="exec WorkCollaboration.dbo.SelectPersonalTimeReport @SupContactIdParam = " & Number.ToText(SupContactIdBiParam) & "])

c) = Sql.Database("Momo", "WorkCollaboration", [Query="exec WorkCollaboration.dbo.SelectPersonalTimeReport @SupContactIdParam = " & Int16.From(SupContactIdBiParam) & "])

I would be very pleased if someone could help. I guess it is a datatype problem.... my id in database is type int. My Parameter definition is type any

Thanks for your advise how to manage this small last step

[v-shex-msft · Accepted solution]

HI @obc4758,

What connection mode are you worked? AFAIK, power bi does not allow you to invoke SP in directquery mode.
In addition, the issue seems related to your function structure, please remove the right " to fix the function usage:

let
    query_SP =
        "exec WorkCollaboration.dbo.SelectPersonalTimeReport @SupContactIdParam = "
        & Text.From(SupContactIdBiParam),
    Source =
        Sql.Database(
            "Momo",
            "WorkCollaboration",
            [Query = query_SP]
        )
in
    Source

Regards,

Xiaoxin Sheng