There are many tools available for providing data to digital analysts. Some allow for streaming available data, while other sources face hurdles with every step. However, when hard-to-get information is put into a Power BI report, which requires regular updates, the process can seem like a waste of time.
Luckily, there is a solution.
Finding free options is challenging, but through the creative use of several Microsoft Office 365 tools, you can automate Power BI datasets. Many variations exist due to the flexibility of these devices, but we will be focusing on just one of them.
In order to accomplish this, the following tools are needed:
The process involves five main stages :
Dataset is Created –> Power BI Report is Created –> Data is Delivered –> Data is Moved –> Dataset is Updated
The format of the delivered file is important. It is critical to create tables—or named ranges—around the data, and ensure that those data models will either expand with the data if it grows, or set the ranges large enough to encompass the data in the future. Using tables and named ranges will also allow for multiple sources of data to be placed on each Excel worksheet if the data source allows.
When the dataset is completed, it needs to be scheduled for delivery to an SFTP by the data provider. In this case, the SFTP is simply the option chosen, but there are many possibilities for hosting the data—including email.
Once this initial dataset is created and scheduled, it needs a place on the Office 365 landscape. In this example, SharePoint was chosen as the source, and the file was placed there.
Open the dataset on SharePoint using Excel, not Excel Online, and in the “Info” tab of the file, click on the file where it is located. Choose “Copy path to clipboard.” In the Power BI report, create a new web connection, paste in the path as a URL, and delete ?web=1 from the end of the string. Now the Power BI Desktop file is connected to the dataset on SharePoint, and when it’s published, the connection will carry over.
Once the Power BI Desktop report is completed, publish the report to the proper workspace to share it with stakeholders.
If the report is published, the dataset may not refresh. If this happens, go to the workspace with the report and choose “Datasets.” Then, under the actions icons, click on the ellipses and choose “Settings”. This is where data source credentials and scheduled refresh are housed.
Under “data source credentials,” there will be a list of connections and their type that will need credentials to connect to the data source. There are two different kinds of connections. The connections with a blank space between the connection type and “Edit Credentials” have an accepted set of credentials. The ones with an “X” next to them are the credentials added based on the company’s security policy. In this case, we used OAuth2.
Once all the credentials have been validated, the yellow warning will disappear and the scheduled refresh section will no longer be grayed out.
The “Keep your data up to date” switch will need to be turned on, but then the dataset refreshes can be scheduled to meet the needs of the stakeholders.
More about connecting Power BI to SharePoint files can be found here.
Data has been delivered to the specified location
In this example, the data is delivered by a third party in an Excel format determined by the analyst to an SFTP. Depending on what tools are providing the data, this can be the hardest part to figure out. Ideally it can be automatically sent via the data provider to meet the analyst’s needs.
This is perhaps the most important step, since this is where the automation part begins. Microsoft has a tool called Flow that is used to automate normally manual processes. In this case, Flow will periodically check the SFTP in the designated directory—frequency is determined by the subscription level—to see if a file has been added or modified. There are many triggers for Flows, but in this case, it will look for new or edited files.
When this particular Flow triggers, it will look at the name of the new file and check it against the files that are on the SharePoint site created to host Power BI datasets. If it finds a file with the same name, it will update that file with the information from the file on the SFTP.
The process of setting up a Flow can be complicated, and will take research and testing to make sure it works according to the settings necessary to meet a company’s configuration. Below is an outline for how the example Flow should be set up:
Based on the settings of the SFTP, additional actions may be needed, such as deleting the file on the SFTP. Also, adding notifications if the “Update file” or “Delete file” actions fail is advisable so proper corrections can be made.
Dataset is updated via Scheduled refresh
With the dataset on the SharePoint site updated, and the dataset in Power BI Service set to have a scheduled refresh, the report will update as scheduled. Take into consideration that based on the size of the file it can take a while for the dataset to refresh, so it is advisable to set the scheduled refresh at least an hour or two before stakeholders expect the report to be refreshed.
The creative use of tools that many companies provide can reduce costs and save time. The lack of APIs, or “on-premises gateways,” can hinder a company’s ability to ingest data that is not held in a database Power BI can connect with. Enabling the capability for nearly anyone to automate their Power BI reports allows even a small business to take advantage of the benefits of automation.
In a world where every contributor is expected to give more, finding ways to improve productivity is paramount.
Platform Migrations: The Good, The Bad, And The Ugly by Davin Anderson