Forum Discussion
Passing PowerBI Parameter To A Stored Procedure
Hello everyone,
I am trying to create parameters within PowerBI so that I can pass the values to the parameters of a stored procedure.
This is the code for the procedure, which uses the AdventureWorksDW2019 database:
CREATE OR ALTER PROCEDURE [dbo].[TestProcWithParameters]
@CustomerKey INT
, @GeographyKey INT
AS
BEGIN
SELECT
[DC].[CustomerKey]
, [DC].[FirstName] + [DC].[LastName] AS [CustomerName]
, [DC].[GeographyKey]
, [DG].[EnglishCountryRegionName]
FROM [dbo].[DimCustomer] [DC]
INNER JOIN [dbo].[DimGeography] [DG]
ON [DC].[GeographyKey] = [DG].[GeographyKey]
WHERE
[DC].[CustomerKey] = @CustomerKey
AND [DC].[GeographyKey] = @GeographyKey;
END;
I'm attaching screenshots of the parameters to this post.
I followed the advice of this post, but I get an error:
let
Source = Sql.Database("localhost", "AdventureWorksDW2019", [Query="EXECUTE TestProcWithParameters " & CustomerKey & ", " & GeographyKey])
in
Source
Expression.Error: We cannot apply operator & to types Text and Number.
Details:
Operator=&
Left=EXECUTE TestProcWithParameters
Right=1
The error tells you exactly what the issue is.
If you want IDs as part of your query string, they need to be in text format. So you have two options
- Define the parameters as text-valued rather than numbers.
- Convert the number to text in your query.
"EXECUTE TestProcWithParameters " & Text.From(CustomerKey) & ", " & Text.From(GeographyKey)
4 Replies
- AlexisOlsonSuper User
The error tells you exactly what the issue is.
If you want IDs as part of your query string, they need to be in text format. So you have two options
- Define the parameters as text-valued rather than numbers.
- Convert the number to text in your query.
"EXECUTE TestProcWithParameters " & Text.From(CustomerKey) & ", " & Text.From(GeographyKey)- AnonymousNot applicable
Thanks. I actually didn't need to change the parameters. Just adding "Text.From" got it to work. I'll see if I need to change them as I build the visuals.
- AlexisOlsonSuper User
Sorry if I wasn't clear. I gave two options that you could pick from. You don't need to do both. Just go with whatever makes more sense in your use case.