Passing PowerBI Parameter To A Stored Procedure

[Anonymous]

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:

https://community.powerbi.com/t5/Desktop/Execute-SQL-Server-stored-procedure-input-parameters-with-p...

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

[AlexisOlson · Accepted solution]

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

1. Define the parameters as text-valued rather than numbers.
2. Convert the number to text in your query.

"EXECUTE TestProcWithParameters " & Text.From(CustomerKey) & ", " & Text.From(GeographyKey)​

[Anonymous]

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.

[AlexisOlson]

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.

[Anonymous]

Ah, ok. Not a problem. The other solution may come in handy at some point in the future.