Forum Discussion
To automate data load with system date
Hi All,
I have a dashboard for which data is coming from stored procedures in SQL server. I want my dashboard to have the lastest data.
Can anyone suggest me how yo automate the data load into Power BI with system date.
The stored procedures run when we give system date as input parameter.
Please suggest
HI Anonymous,
It seems like you missed the right 'quotation' in your query that stored the 'store procedures', please add it to confirm if the issue fixed:
Source = Sql.Database( "test", "test", [ Query = "DECLARE @return_value int EXEC @return_value = [dbo].[usp_Response_SLA] @date = " & Date.ToText(Date.From(DateTime.LocalNow()),"MM/dd/yyyy") ] )snapshot
Notice:
1.'02-09-2021' will be generated by the right part M query function, you not need to add them to your query.
2. I also modify the generated date functions to get the same format as you shared.
Regards,
Xiaoxin Sheng
6 Replies
- amitchandak
Super User
Anonymous , refer if this older solution can offer any help
Hi Anonymous,
I think you can try to parameterize your connection string to send the current system date and use it as a parameter to get data.
M query DateTime.LocalNow
Regards,Xiaoxin Sheng
Hi Anonymous,
You can invoke your store procedures with the function that I mention.
Sample steps:
1. Past the 'Stored Procedure' string into the SQL statement of your connector: (advanced option)
EXECUTE [dbo].[xxxxxxx] @date= xxxxx
Import data from a database using native database query
2. Enter to 'query editor' and navigate to the 'advanced editor' to modify the steps to concatenate the raw string with 'DateTime' functions:
Source = Sql.Database( "test", "test", [ Query = "EXECUTE [dbo].[xxxxxxx] @Date= " & Text.From(DateTime.LocalNow()) ] )Power Query and Stored Procedures with Parameters
Regards,
Xiaoxin Sheng
