Forum Discussion
Getting data from stored procedure into power bi native query for years using incremental refresh
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.
Change the format of your RangeStart and RangeEnd parameters to DateTime. I assume you want to use them for incremental refresh.
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
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
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"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.
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.
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.