Automate Power Query in One Click

2. The Problem: You are living through an exhausting situation: inheriting someone else’s system and feeling that, instead of automating, you are simply “automating the supervision.” Having to trigger a Power Automate Desktop bot every morning to click things for you isn’t true automation; it is simply delegating manual work to a machine, but with the same mental load and workflow disruption.

3. The Action Plan:

4. Decouple the process from your computer: The key is to stop depending on a file opening in your Windows session for data to move.

5. Reconfigure the source to the cloud: Transform the access path from “Local Folder/Synced OneDrive” to a “SharePoint URL” so Power BI can read the file directly from the internet without intermediaries.

6. Native cloud scheduling: Set up a Scheduled Refresh directly in the Power BI service, completely eliminating the need to run bots or open Excel.

7. Detailed Implementation: The main mistake you are making is using the file path as if it were on your hard drive (example: C:\Users\YourName\SharePoint\File.xlsx). This forces the Power Query engine to look for a local process. To fix this, follow these exact technical steps:

Step 1: Get the direct file URL from SharePoint Do not use the “Share” button in SharePoint, as that generates a viewing link, not a data link.

1. Open your SharePoint library in your browser.
2. Locate the Excel file used as the source.
3. Click on the three dots (…) and select “Details.”
4. In the side panel, scroll down to find “Path” and copy the link that appears there. This link must end in .xlsx.

Step 2: Modify the query in Power Query (M Language) Now you need to tell your Power BI report to no longer look for a file on your PC, but for a web address.

1. Open your Power BI Desktop file.
2. Go to “Transform Data” to open the Power Query Editor.
3. In the left panel, select the query that reads the Excel file.
4. In the formula bar (if you don’t see it, enable it under the “View” tab), you will see something like this: = Excel.Workbook(File.Contents("C:\Users\YourUser\SharePoint\file.xlsx"), null, true)
5. Replace File.Contents with Web.Contents and paste the URL you copied in Step 1. The formula should now look like this: = Excel.Workbook(Web.Contents("https://yourcompany.sharepoint.com/sites/your-site/documents/file.xlsx"), null, true)
6. Press Enter. You will likely see a yellow “Security Warning” message.

Step 3: Configure access credentials (Crucial) For the Power BI server to access SharePoint without your manual permission every morning, you need to provide it with a master key.

1. When the “Web Access” dialog box appears, select the “Organizational Account” tab. Do not use “Anonymous” or “Windows.”
2. Click “Sign in” and enter your Microsoft 365 credentials.
3. Once logged in, click “Connect.”

Step 4: Publish and Schedule

1. Publish the report to your Power BI Service workspace.
2. Once published, go to the Power BI web portal.
3. Find the “Dataset” for your report, click on the three dots, and select “Settings.”
4. Look for the “Data source credentials” section.
5. Click on “Edit credentials,” ensuring the authentication method is OAuth2 and the privacy level is set to Organizational.
6. Now, expand the “Scheduled Refresh” section and enable it. Configure your days and times (for example, 8:00 AM).

With this, the process is 100% autonomous. Microsoft’s server communicates with SharePoint at 8:00 AM, downloads the changes, and updates the report without you even having to turn on your computer.

5. Suggested Tool: Power BI Service (Cloud) + SharePoint Online. This is the ideal solution because it requires no new installations or extra licenses if you already use Microsoft 365. It is a native “cloud-to-cloud” integration. By using the Web.Contents connector, you eliminate dependency on a “Gateway” and your PC’s local processes, making the update robust, professional, and, above all, invisible to you.

6. Prompt to copy:

Act as a Power Query M language expert. I have a query that currently uses File.Contents with a local path from a synced SharePoint (example: "C:\Users\Name\SharePoint\file.xlsx"). I need you to write the exact M code to transform this connection to Web.Contents using a SharePoint Online URL, maintaining the Excel.Workbook structure and ensuring compatibility with OAuth2 authentication in Power BI Service. Do not explain theory; just give me the code block ready to replace in my Advanced Editor.