To automate data load with system date

[Anonymous]

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

[v-shex-msft]

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

[Anonymous]

Thank you for the reply can you please elaborate the steps in details.

[v-shex-msft]

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

[Anonymous]

Hi @v-shex-msft ,

Thank you for the reply, I tried using this but its thorwing below error.

The below is my Strored Procedure in Power BI

DECLARE @return_value int

EXEC @return_value = [dbo].[usp_Response_SLA]
@date = '02-09-2021'
& Text.From(DateTime.LocalNow())       -----------------I added as per your suggestion here

SELECT 'Return Value' = @return_value

[v-shex-msft · Accepted solution]

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

[amitchandak]

@Anonymous , refer if this older solution can offer any help

https://community.powerbi.com/t5/Desktop/Power-bi-Incremental-Refresh-and-Stored-Procedures/td-p/1538140