Forum Discussion
Getting data from stored procedure into power bi native query for years using incremental refresh
Hello,
I am trying to get data from stored procedure using Native query but when i write this below code it gives me the output of whole Database with all the tables & functions and i am unable to view the native query or only the columns i am trying to get information from stored procedure.
let
Source = Sql.Database("server", "database"),
Native = "Value.NativeQuery(Source,""EXEC dbo.Usp_storedproc @vstartwkdate = '"" & Date.ToText(RangeStart) & ""' ,@vendwkdate= '"" & Date.ToText(RangeEnd) & ""'"", @vLoginEmployeeID = User_name())"""
in
Native
Can anyone please suggest a way to get data passing the above paramaetrs in the stored procedure for couple of years thanks
Please provide sample data.
NOTE 1: Your Native step is just a string. You need to remove the outermost double quotes.
NOTE2: Instead of Value.NativeQuery you can already specify the Query in the Source step
NOTE3: Date.ToText is not optimal here. You want to code the exact format that is expected from SQL server, ideally ISO-8601.
let Source = Sql.Database("server", "database",[Query="EXEC dbo.Usp_storedproc '" & DateTime.ToText(RangeStart , "yyyy-MM-dd") & "','" & DateTime.ToText(RangeEnd , "yyyy-MM-dd") & "',''"]) in SourceYes you did but it was throwing the same error and i realized power query was case sensitive so modified it to to query to see if its working but now its good i missed the brackets but getting different error now
Change the format of your RangeStart and RangeEnd parameters to DateTime. I assume you want to use them for incremental refresh.
No, user selection won't help you there (it comes much later in the process). Incremental refresh happens on the Power BI service.
Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Learn
Neither #"Filtered Rows" nor #"Filtered Rows1" are necessary. The Power BI service will take care of setting the values for RangeStart and RangeEnd for the partitions that you specified. Please refer to the documentation.
If your SP comes up blank then you need to check your SP.
15 Replies
Please provide sample data.
NOTE 1: Your Native step is just a string. You need to remove the outermost double quotes.
NOTE2: Instead of Value.NativeQuery you can already specify the Query in the Source step
NOTE3: Date.ToText is not optimal here. You want to code the exact format that is expected from SQL server, ideally ISO-8601.
Thank you I tried various ways to get the data from stored procudure based on the parameters but View My Native Query is disabled no matter what i do and instead i am getting all the Database tables & functions as output
let
Source = Sql.Database("server", "database"),
NativeQuery = "Value.NativeQuery(Source,""EXEC dbo.Usp_storedproc @vstartwkdate ,@vendwkdate',@vLoginEmployeeID"", [vstartwkdate= RangeStart,vendwkdate= RangeEnd,vloginemployeeid =''])"
in
NativeQueryI tried to remove the outer "" but it says Error Token expected.
let Source = Sql.Database("server", "database",[Query="EXEC dbo.Usp_storedproc '" & DateTime.ToText(RangeStart , "yyyy-MM-dd") & "','" & DateTime.ToText(RangeEnd , "yyyy-MM-dd") & "',''"]) in Source