Tips and Tricks

Use local files as Power BI data sources without configuring a Gateway

Written by MEL | Feb 21, 2019 10:01:00 AM

If you build a report in Power BI Desktop and publish it to the Portal, you need to refresh the Dataset from time to time. But if one of your Data Sources is a local file, Power BI will force you to configure a Gateway for executing the refresh of the Dataset. So, you need a computer or server, that should work 24/7 to set up the Gateway and have your Dataset always up-to-date.

But if you have, for example, only one report pack, and only one Local Data Source like an Excel file, you probably don’t want to configure the Gateway and keep your computer working 24/7.

So, you can use OneDrive for Business links in Power BI Desktop. It turns your Local Data Source into a Web Data Source and allows you to do the Dataset Refresh without configuring the Gateway.

There are four easy steps to do this:

  1. Store your Excel file in OneDrive;

  2. Open the file stored in OneDrive in Excel;

  3. Copy the file’s link from File > Info to clipboard;

  4. Add new Web Data Source (Get Data > Web). Paste copied URL and remove the ?web=1 string  at the end of the link. Click OK.

You can visit https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links to get more detailed information how to use OneDrive files in Power BI.

Now your ex-local Excel file is a Web Data Source and you can always refresh the report without additional Gateway settings.

Happy Learning.

Best Regards, 
Team of Managed Services Department
Trusted IT Group

support@trusteditgroup.com