Power BI：自定义函数查询能否在Power BI Online中自动刷新？

Short answer: Yes, it can—but you need to meet a few key requirements first. Let’s break down what you need to check and configure to make auto-refresh work:

1. Get Your SQL Data Source Setup Right in Power BI Online #

• If your mda SQL Server is on-premises, you’ll need an on-premises data gateway installed and linked to your Power BI workspace. The credentials you use for the data source (either the gateway service account or a dedicated SQL account) must have:
  • Permissions to execute the [dbo].[sqSupplierBalances] stored procedure
  • Read access to all underlying tables/views the procedure references (like Suppliers and vSupplierBalances)
• For cloud SQL instances, confirm you’ve whitelisted Power BI’s IP ranges and that your credentials have the necessary database permissions.

2. Handle the Period0 Parameter for Auto-Refresh #

Your function depends on the Period0 text parameter, which needs a clear value during auto-refresh:

• For a fixed period (e.g., always refresh "2024-Q2"), set a default value for the parameter in Power BI Desktop before publishing. You can also update this fixed value later in the dataset’s settings in Power BI Online.
• For a dynamic period (e.g., current month/quarter), replace the manual parameter input with a Power Query expression that calculates the period automatically. For example:

  let CurrentPeriod = Date.ToText(Date.StartOfMonth(DateTime.LocalNow()), "yyyy-MM")
  in SQLSource(CurrentPeriod)
  

  This way, the parameter value generates itself during each refresh, no manual input required.

3. Validate Your SQL Query Compatibility #

Your stored procedure call syntax in Power Query is valid, but double-check:

• The string concatenation for @Period = '"& Period0 & "' produces valid SQL. For example, if Period0 is "2024-Q2", the generated SQL should read @Period = '2024-Q2' (no syntax typos or missing quotes).
• The stored procedure doesn’t use features that break in non-interactive Power BI Online sessions (like interactive prompts or temporary table logic that fails without user input). Test the procedure directly in SQL Server Management Studio first to confirm it returns expected results.

4. Test the Refresh Workflow #

• First, test the refresh in Power BI Desktop with your parameter to ensure it pulls data correctly.
• After publishing to Power BI Online, trigger a manual refresh first. If it fails, check the refresh history (under Dataset > Refresh history) for specific error messages—this will tell you if it’s a gateway issue, permission problem, or parameter misconfiguration.

Once all these steps are sorted, you can set up a scheduled auto-refresh in Power BI Online’s dataset settings, and it will run with your configured parameter value automatically.

内容的提问来源于stack exchange，提问作者Warric Ritchie