Getting data from stored procedure into power bi native query for years using incremental refresh

[dbollini]

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

[lbendlin · Accepted solution]

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.

[lbendlin · Accepted solution]

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

[dbollini · Accepted solution]

Yes 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

[lbendlin · Accepted solution]

Change the format of your RangeStart and RangeEnd parameters to DateTime.  I assume you want to use them for incremental refresh.

[lbendlin · Accepted solution]

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

[lbendlin · Accepted solution]

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.

[dbollini]

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
NativeQuery

I tried to remove the outer "" but it says Error Token expected.

[lbendlin · Accepted solution]

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

[dbollini]

Thank you for the answer and i am using the same way you suggested and i am getting Expression error that query wa snot recognized before my query did not have errors but at the same time it was not working either before

[lbendlin]

I wrote Query, not query.  Power Query is case sensitive.

[dbollini · Accepted solution]

Yes 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

[lbendlin]

Please show a sanitized version of your Power Query code.

[dbollini]

Expression.Error: We cannot convert the value #date(2023, 1, 1) to type DateTime.
Details:
Value=1/1/2023
Type=[Type]

[lbendlin · Accepted solution]

Change the format of your RangeStart and RangeEnd parameters to DateTime.  I assume you want to use them for incremental refresh.

[dbollini]

Yes i Updated it to Date time now it worked Thank you so much for your help and i think there is condition in teh stored procedure which allowes only one month data to load so i ahve to set up incremental refresh so it should filter data based on the user selection of dates

[lbendlin · Accepted solution]

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

[dbollini]

I added the INcremental refresh like in the above link and modified Rangestart and End parameters to jan 1 till sep but my stored procedure is coming as blank data so shd i do anything differently

let
Source = Sql.Database("server", "database",[Query ="EXEC dbo.Usp_storedpproc '" & DateTime.ToText(RangeStart , "yyyy-MM-dd") & "','" & DateTime.ToText(RangeEnd , "yyyy-MM-dd") & "',''"]),
#"Filtered Rows" = Table.SelectRows(Source, each [DisplayDate] >= #datetime(2023, 1, 1, 0, 0, 0) and [DisplayDate] < #datetime(2023, 9, 30, 0, 0, 0)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [DisplayDate] >= #datetime(2023, 1, 1, 0, 0, 0) and [DisplayDate] < #datetime(2023, 9, 30, 0, 0, 0))
in
#"Filtered Rows1"

[lbendlin · Accepted solution]

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.

[dbollini]

Thank you and looks like Sp is set to default to bring only one month data will talk to database person to adjust the time period.

[lbendlin]

It is possible to set Power BI Incremental Refresh to monthly partitions  but it may be too detailed.  Depends on the amount of data per partition, really.  250M rows per partition is a good guidance.