If you used an excel file in a local source (or even OneDrive for Business, but sourced locally), you need to do one extra step to make your query refreshable without the need to the gateway. Here in this blog post, I’ll explain an easy way to do that change. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
On-Premises(Local) Excel Source
In the example below, I have created a Power BI report with Get Data from Excel, and I used the Excel file that is located in my OneDrive for Business directory:
After building the report, and publishing it to the Power BI service, in the schedule refresh, you will get to the place that you need to connect it to a gateway
You cannot schedule refresh the report unless you set up a gateway! If you don’t know what the gateway is, here is brief information about it: Gateway is an application that creates a connection from the Power BI dataset in the service to the local domain (on-premises) data source. Here in this article, I explained everything you need to know about the gateway.
As you see in the above expression, the gateway is needed for data sources that are on-premises. One Drive for Business is not an on-premises data source. So why do you get this issue of needing to set up the gateway? The reason is that our file is not sourced from OneDrive for Business. It is sourced from a local folder. To see where the data source of your Power BI leaves, click on Data Source Settings under the Transform Data in the Power BI Desktop.
Here you can see all the data sources, and you can click on any of those to change if you want to.
As you can see, the source in the above file is a local file in my local folder in C drive! Even though I have selected it from the OneDrive for Business folder, it is still pointing to the local folder representative of that. and as long as the local folder is the source, the gateway is need to get it refreshed in the service. Here is the quick solution for you.
Solution: Change Source to Web Query
You can change the source of your excel file to a web query, but you need to have the web URL address of that file. Here is how you can get it:
Find the Web URL Source
Open the Excel file locally, and then go to File menu, under Info, right-click on Open File Location, and then Copy Path.
The path should look like this (paste it into a notepad window):
https://xyz-my.sharepoint.com/personal/reza_radacad_com/Documents/Data Sources
You need to use this path now as the source
Change the Source to the Web.Contents
Go to Transform Data section of Power BI Desktop
In the Power Query Editor window, click on the Source step of your query.
If you don’t see the formula bar, go and enable it in the View tab;
Now change the File.Contents(“…”) part of the formula above with Web.Contents(“…”)
here is how I change mine from:
= Excel.Workbook(File.Contents("C:\Users\RezaRad\OneDrive - RADACAD\Data Sources\Pubs.xlsx"), null, true)
to this:
= Excel.Workbook(Web.Contents("https://radacad-my.sharepoint.com/personal/reza_radacad_com/Documents/Data Sources/Pubs.xlsx"), null, true)
Note that the address inside the Web.Contents are coming from the path that you have copied in the notepad in the previous step plus a forward slash (/), and then the file name and extension.
Web.Contents(“<path copied in the previous step>/filename.xlsx”)
And note that the Web should be written with capital “W”, and lowercase “eb”.
After doing this change for all your queries that are coming from the file from OneDrive, then click on Close and Apply in the Home tab.
Now this time, after publishing it to the service, in the Schedule Refresh tab, you can see that the gateway configuration is disabled, and you just need to Edit Credentials under Data Source credentials.
Set the credentials with OAuth2, and Sign in with the account which has access to the OneDrive for Business folder;
Now you can schedule the dataset to refresh (without the need to gateway):
Or you can run the refresh manually to test if it works fine.
I hope the method mentioned in this quick blog post, helps you in your Power BI implementation.
Video
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Hi Reza – thank you for this – it was very helpful.
What if your data source also contains a SQL server for importing the data? Are there other steps I would need to take?
Thank you
Chris
For SQL Server, if it is on-premises SQL Server, then you need the gateway for sure.
if it is Azure SQL DB, you just need a server name or IP, with no gateway needed.
so either way, no change is needed in Power Query
Cheers
Reza
Great solution Reza👍🏼
Do you know if it’s possible when using dataflows with on prem data?
/Sainey
Hi.
Yes, the same method can be implemented using Dataflows.
Cheers
Reza
Hi Reza, first of all, Happy New Year! Wish you and your family a health and happy new year of 2020.
Thanks for the detailed instruction in this post. I’ve tried to do something similar… changed the data source to SharePoint Folder; and publish the report to on-premise report server. However, when I tried to edit the credential, it keeps telling me my credential is incorrect. I was sure I had input my password correctly. Nevertheless, I am not sure if I have input the rest correctly. Wondering if you could point me to the right direction. Appreciate it.
Thanks.
MF
Hi MF
Happy New Year!
The implementation you are talking about seems different. You are working with Folder (SharePoint Folder), and your report is going to be hosted on the Power BI report server.
However, I think that still should work as long as the data source is supported (I haven’t tested SharePoint folder data source on-prem to see is it working or not)
What credentials did you use? have you selected Microsoft (OAuth) credentials?
Cheers
Reza
Hi Reza. May I first wish you a happy new year. Thank you a gain for this great post. Refreshing my report is a kind of nightmare for me.
I didn’t implemented yet your proposal but what is specific to my own case is that I used to be implementing function to folder from my OneDrive for Business contains my daily files.
Can I still be using your described procedure?
Thank.
Hi.
Happy New Year to you too 😊
Yes, you can still use this approach.
Or, alternatively, you can use SharePoint.Files function in Power Query to get all the files from the OneDrive for Business folder you want. I might write a blog post about that method too.
Cheers
Reza
SharePoint.Files function returns 400 bad request errors – no matter what you use.
The trick for SharePoint.Files or SharePoint.Contents are to go to the root URL and then navigate from there. Check out my blog post about that method here.
Cheers
Reza
OAuth2 is not available for me – it’s not in the dropdown list. Only Anonymous, Windows and Basic and none of them let me through.
What is the URL you are trying?
Cheers
Reza
Thank you for puting me on the right track. The URL was wrong apparently although I copied it from Power BI Service with the copy url button. For some reason “:x:/r/” was inserted between “my.sharepoint.com” and “personal”. After removing this Oath2 appeared in the dropdown list.
Great to see it is resolved 🙂
Excelente.! mejor explicado no lo había podido encontrar y me ha servido mucho. Muchas gracias
I’ve done this and my report refreshes although now its only pulling in the first line of data from my spreadsheet instead of all 50 rows. Any idea why that is?
can you share your M script here?
Hi,
For this solution, if I have 50 datasets that connect to OneDrive and my company requires me to change password every 3 months… does that mean each 3 months I have to update credential manually for 50 datasets? Is there a better way to f=do this?
Are all of those 50 datasets in one folder? if yes, then all you need to do is just once create the connection to that folder, and then other queries can be reference to that folder and navigating to the files.