Scheduling automatic data refresh in Power Query ensures that your data in Power BI reports or Excel workbooks stays up-to-date without manual intervention. Here’s how you can schedule data refresh:

In Power BI Service (Cloud-based):

1. Publish Your Report to Power BI Service:
- Ensure your Power BI Desktop report (.pbix file) is saved.
- Publish your report to Power BI Service by clicking on `Publish` in Power BI Desktop and signing in to your Power BI account.

2. Set up Scheduled Refresh:

- Open your report in Power BI Service (app.powerbi.com).
- Go to the dataset that your report is using. (Navigate to `Datasets` in the left navigation pane.)
- Click on the dataset to open its settings.

3. Configure Refresh Schedule:

- In the dataset settings, go to the `Scheduled refresh` section.
- Toggle `Keep your data up to date` to enable scheduled refresh.
- Set the refresh frequency (daily, weekly, or custom schedule).
- Specify the time when you want the refresh to occur. Note that times are based on UTC.

4. Apply Changes:

- Click `Apply` to save the refresh schedule settings.

5. Monitor Refresh History:

- After setting up scheduled refresh, you can monitor the refresh history and status in the `Scheduled refresh history` section of the dataset settings.
- Review any errors or warnings that occur during refresh to troubleshoot and ensure data integrity.

In Power BI Desktop (for Testing and Development):

1. Enable Local Data Refresh:

- Open your Power BI Desktop report (.pbix file).
- Go to `File` > `Options and settings` > `Options`.
- In the `Options` dialog box, go to `Data Load` and enable `Allow data preview to download in the background`.
- This setting allows Power BI Desktop to refresh data when the file is opened.

2. Schedule Refresh When Publishing to Power BI Service:

- When you publish your report to Power BI Service, follow the steps outlined above in the Power BI Service section to set up scheduled refresh for the dataset.

In Excel (for Excel Workbooks):

1. Enable Data Refresh:

- Open your Excel workbook (.xlsx file) that contains data loaded via Power Query.
- Go to `Data` > `Connections`.
- Select the connection that uses Power Query, and click `Properties`.
- In the `Connection Properties` dialog box, go to the `Usage` tab and enable `Refresh data when opening the file`.

2. Schedule Data Refresh:

- For more advanced scheduling, such as refreshing data at specific intervals or times:
- Go to `Data` > `Queries & Connections`.
- Right-click on the query that you want to schedule refresh for and select `Properties`.
- In the `Query Properties` dialog box, go to the `Usage` tab and adjust the `Refresh data` options.

3. Use Power BI Publisher for Excel (Optional):

- If your Excel workbook is published to Power BI Service, you can use Power BI Publisher for Excel to set up scheduled refresh in a similar manner to Power BI reports.

Considerations:

- Data Source Connectivity: Ensure your data sources support scheduled refresh. Some data sources may require additional setup or configurations for scheduled refresh to work correctly.
- Permissions: You need appropriate permissions (e.g., dataset admin or workspace admin roles) to set up and manage scheduled refresh in Power BI Service.
- Error Handling: Monitor refresh history regularly to address any errors or warnings that may affect data refresh.

By scheduling automatic data refresh in Power Query, you can maintain data accuracy and ensure that your reports and dashboards in Power BI or Excel reflect the latest information from your data sources.