Throughout my career as a controller in the Telecom industry, I mostly used Excel as a tool. In addition to all the advantages that Excel brings, there are also disadvantages that we wanted to avoid.
One of the biggest disadvantages of Excel were repetitive tasks and the possibility of errors, which was reducing quality and speed of reporting and was also taking a lot of time for data preparation. Unfortunately, data security was very low.
Considering that controller’s task is to analyse the company's operations and to help the management and the board to make the right decisions by using his analyses, in 90% of the time, this task was reduced to data preparation instead of analysis.
Power BI Add-ins
As Power BI Add-ins for Excel came to the stage (Power Pivot, Power Query), we decided to try out all the features offered to speed up the reporting process. Power Query Add-in made possible for all the repetitive work to be done by Excel itself. All data transformations, from data cleaning, pivoting, filling empty cells with real values etc., were done in Power Query. Once we made all the transformations, there was no need to do them again, because Power Query remembered all our steps and every new data refresh - Power Query would do all of that for us. With Power Query, we got the feature to load data from several different sources, and then later to display them in Excel through Pivot Tables. We were no longer limited to 1 million rows, which is the limit of Excel, where we needed to do data models (relate them with different tables) from where we would create measures or add calculation columns to, for example, further describe the data.
Power BI
To be able to create measures, we had to learn programming language DAX, which is a continuation, or in other words, extension of Excel formulas.
As Power BI application became a separate entity, which contains Power Query and Power Pivot and more advanced visualization, transition to Power BI was a logical way to go. Power BI enabled our analyses to be faster and of higher quality, the security of our reports to be improved, and for us to determine who can access data and reports through Row Level Security. For example, employee salary costs could now only be seen by department heads, and for their department employees only, even though everyone would be using the same report. Data could also be accessed by internal users via Excel, by connecting to cloud data models and using the data for some additional analysis or business cases. All reports were refreshed in the Cloud and were updated daily or weekly.
To make use of Power BI and its capabilities even more, we started to educate internal users of all the possibilities of Power BI so that they can improve and automate their work and to replace 90% of data preparation time with 90% of data analysis time.