Eugene Lebedev is a Power BI consultant by background. Before founding Vidi Corp in 2021 he created Power BI reporting for Autodesk. His Power Bi reports were used by VPs of Finance and C-suite of Autodesk. As part of Vidi Corp, Eugene created dashboards for Google, Teleperformance, Delta Airlines and 200+ clients worldwide.
Many companies start with Power BI because they want to get rid of manual Excel-based reporting. Power BI definitely can help to automate manual reporting, there are many examples of automating reports in Power BI. However, the process of automation is not simply about Power BI. You also need to know how to use it.
In this article, I will discuss how to automate Excel reports using Power BI. If you are interested in a practical example, here is our client talking about how exactly we automated her Excel reports using Power BI.
I will use this case study to explain how to automate every bit of your reporting using Power BI including:
Automating data extraction: The first step towards report automation is to extract data automatically. This would remove the need to copy and paste data from one spreadsheet to another.
In order to automate Power BI, you can use Power Query. Power Query is available both in Excel and Power BI. You can find a beginner tutorial for Power Query here on our website.
Power Query in Power BI allows you to extract data automatically from 100+ data sources. Power Query in Excel covers fewer data sources, but it still covers some.
If you can not see your data source inside of the Power Query get data menu, you have the option to build a custom Power Query connector. We have built those connectors for many of our clients before. A demonstration of how these connectors work is below.
The custom connector essentially extracts data directly from your source by using an open API.
Remember the client from the beginning of the article? They had their data in Quickbooks Online and they used to extract this data manually.
We noticed that Quickbooks Online had an open API which is documented. We then created a custom connector to extract data from it.
If you want to check whether the same process is possible in your case, I recommend googling the name of your data source and searching for available API documentation. For example, you could google “Quickbooks Online API Documentation”. If you see any results, this means that you can create a custom connector.
You have several options with custom connectors:
Once your data is extracted, you need to automate the process of transforming it to a workable format. Too many companies do this process manually by removing columns, moving them around the spreadsheet, deleting rows, etc.
All of these data manipulations can be achieved automatically by using Power Query. We have an article that describes how to do these manipulations step by step. Power Query applies data transformation steps to your tables and then when you refresh your data, it applies them again every time. This way you don’t have to do the same data manipulation manually every time.
Take the client that I mentioned at the beginning of this article for example. They used to spend 14hr per week transforming data once extracted. They were aggregating some rows of data together, applying filters and performing manual calculations. All of these processes are now replaced with Power Query.
Once you have automated your data extraction and data transformation, you can set up an automated refresh. Power BI has a scheduled refresh feature. It allows you to set a timer for your data refresh for up to 8 times per day (assuming you have a Power BI Pro license).
Configuring scheduled refresh is a pretty quick job which takes less than an hour. However, it all depends on your data source. For some data sources, you also need to configure a personal data gateway. This is a piece of software that you install on your computer which allows the data to flow from your data sources to your computer to Power BI Service.
In the case of our client, we set up the Power BI report to refresh every working hour e.g. 8 times per day.
If you struggle to set up automated refresh, I recommend you speak to a professional consultant. At Vidi Corp, we offer 1 hour on-the-call support to help clients resolve roadblocks. Contact us to book your consultation!
Last but not least, you need to make sure that your reports are low maintenance. You can do this by avoiding hard-coding as much as possible.
Hard-coding is a process for using specific values in your code. For example, if you filter all your visuals by date = “14 June 2023”. If you do this and you open your reports later, you will have to manually change the date filter.
Instead of hard-coding, you can set up a filter to date = “today”. This way you don’t have to manually maintain your Power BI reports every day by changing filters.
The same concept applies to how you write your formulas and apply data transformation steps.
When we built automated reporting for our client, we made sure that whatever formulas we built had as little hard code as possible.
As you can see from this article, the process of Excel report automation with Power BI is all about how you use Power BI. Power BI has the functionality for report automation, but you need to approach the process of report automation in an analytical way: breaking it down step by step.
Now that you know the steps for report automation and you know the functionality, which is available to enable this process, you can take steps in the right direction.
As the next steps, I would recommend planning to apply the process I described to your automate reports in Power BI. Think about:
Once you answer the questions above, read more about the Power BI functionality that we discussed. Specifically:
If you don’t want to spend hours searching for information about these, book a consultation with someone from our team! We can tell you all about the specific functionality that you need. We can also help you to plan your Excel report automation project with Power BI. Contact us to book your consultation!
