Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i have a list of IDs
1000
1001
1002
...
and I want to call a Stored Procedure with each value of that list, appending the results.
I managed to do it with a parameter, but unsure how to interate through the list
Solved! Go to Solution.
Hi @akos_skutovics ,
You should declare a minID parameter and a maxID parameter first when create the stored procedure in sql server, like this:
CREATE PROCEDURE SSALES @MINID varchar(50),@MAXID varchar(50)
AS
Select * from _Customer Where CustomerID >= @MINID AND CustomerID <= @MAXID
GO
In power query, create two parameters based on your IDList:
Modify your query like this:
let
para = (MINID as text,MAXID as text) =>
let
Source = Sql.Database("xxx", "xxx", [Query="exec SSALES @MINID = '" & MINID & "', @MAXID = '" & MAXID & "'"])
in
Source
in
para
When you enter the MINID and the MAXID, you can get the specific list result:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @akos_skutovics ,
You should declare a minID parameter and a maxID parameter first when create the stored procedure in sql server, like this:
CREATE PROCEDURE SSALES @MINID varchar(50),@MAXID varchar(50)
AS
Select * from _Customer Where CustomerID >= @MINID AND CustomerID <= @MAXID
GO
In power query, create two parameters based on your IDList:
Modify your query like this:
let
para = (MINID as text,MAXID as text) =>
let
Source = Sql.Database("xxx", "xxx", [Query="exec SSALES @MINID = '" & MINID & "', @MAXID = '" & MAXID & "'"])
in
Source
in
para
When you enter the MINID and the MAXID, you can get the specific list result:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
What you're trying to do can be very inefficient, especially when the list is long and the procedure that gathers data is not optimized for this workload. Wouldn't it be better to call the procedure once so that it gathers all the data you need in one go and then brings it into Power Query? Then you could easily join the list/table with the data on the right field... or even postpone it altogether?
@akos_skutovics Perhaps try using a Table.AddColumn statement to add a column and the column calls the function.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.