Complete automation with Power BI and Excel Data Sources

Complete automation with Power BI and Excel Data Sources

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:  

  1. Secure File Transfer Protocol (SFTP) – Will serve as the location for the dataset to be dropped to by a third-party tool 
  2. Microsoft Office 365 
  3. Excel in almost any format – Provides the format in which the data will be delivered  
  4. SharePoint – Hosts the Excel files 
  5. Flow – Moves the data from the SFTP to SharePoint 
  6. Power BI – Ingests the data directly from the file on SharePoint and acts as the data visualization and BI tool 

Automating Power BI Datasets

The process involves five main stages : 

Dataset is Created –> Power BI Report is Created –> Data is Delivered –> Data is Moved –> Dataset is Updated

Dataset is Created 

  1. Create the dataset 
  2. Build tables or named ranges around the data 
  3. Schedule the dataset for delivery 
  4. Host the dataset on an Office 365 location 

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.  

Power BI Report is Created 

  1. Connect the dataset on SharePoint to a Power BI Desktop report 
  2. Publish the report to Power BI Service 
  3. Edit data source credentials 
  4. Schedule the refresh 

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 is Delivered 

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. 

Data is Moved

  1. Flow checks to see if there is a new or updated file 
  2. If a new or updated file is present, the file with a matching display name is updated by Flow with the new information 
  3. Additional steps as necessary 

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: 

  1. Create an SFTP connection 
  2. Create a new flow with the following actions: 
  • Trigger the flow when a file is added or modified (SFTP action) 
  • Get file metadata if a file was added or modified (SFTP action) 
  • Update file (SharePoint action) 

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

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. 

Conclusion 

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. 

Interested in learning more? Contact our team today.

sales@us.beyondsoft.com

Connect with us on social media!

LinkedIn

Twitter

YouTube

Check out our other blogs from our team.

Platform Migrations: The Good, The Bad, And The Ugly by Davin Anderson

Testing, Attention Please: App Developers This One Is For You by Marketing

The following two tabs change content below.

Aaron Wheatcraft

Latest posts by Aaron Wheatcraft (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *