SQLDeclare Value in Power BI

[cheid_4838]

I have a large query in SSRS that I would like to convert to power bi. I would like to import the query, but keep getting a "Must declare the scalar variable "@BillTo" and "@StartDate", "@EndDate", and "@StdRate".  How do I go about importing the query into power bi so that I can setup paramaters for these values?  Thanks.

--DECLARE @StartDate AS DATETIME DECLARE @EndDate AS DATETIME SET @StartDate = '10/29/2023' SET @EndDate = '11/3/2023' DECLARE @BillTo as varchar(max) SET @BillTo = 'CATGRI'
--DECLARE @StdRate as money SET @StdRate = 42.00 DECLARE @OTRate as money SET @OTRate = 42.00

, isnull((select top 1 ivd_rate from invoicedetail id (nolock) inner join invoiceheader ih (nolock) on ih.ivh_hdrnumber = id.ivh_hdrnumber where ih.ivh_billto = @BillTo and ih.ivh_deliverydate >= @StartDate and ih.ivh_deliverydate < @EndDate+1 and id.cht_itemcode = 'FSCMI' and ivd_rate <> 0),0) as FSCRate

WHERE ord_billto = @BillTo
AND o.ord_status = 'CMP'
AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1

[vojtechsima]

Hello, @cheid_4838 ,

I would create Parameters in Power Query that you then reference in your query.

Since SQL query in PBI is just plain text, you can do it similry.

Example

"SELECT * FROM XX WHERE status = '"& text_parameter & "' "
Outcome
SELECT * FROM XX WHERE Status ='text'

[cheid_4838]

Thanks for the recommendation.  I am not expert in SQL or power bi.  Would it be as simple as adding "& text_parameter &"" to replace  @BillTo or @startdate?

[vojtechsima · Accepted solution]

@cheid_4838 

yeah sorry,

declare your variables as parameters in Power Query and then replace any mentioning of that variable.

your @BillTo would become bill_to (variable) for example.