This time, dear audience, I decided to go for a visual representative version of our post. In the video above, I am showing how to address one of the main crippling features of the most common type of business report: the Excel spreadsheet. But first, some context.
Nachito Is to Blame for My Persistence in Analytics
In 2014 I came across Nachito, my good friend and coworker at the time. He was one of the very few people that I have ever met that was in complete control of his analytic skills. Many people will argue that they know data analysis and all about insights here and there, but Nachito was onto something else… He was onto the tool that changed my professional life, Nachito was making amazing KPI dashboards out of millions of rows of data… in Excel!
I was losing it when I noticed this feat. At the time, I was a Jr. Consultant still trying to understand how pivot tables worked, and lo and behold, here comes Nachito with his slicer-controlled dashboards to present in front of our top client. But for me, the most impactful piece of innovation from his dashboard was that it was combining multiple databases from our client: financial data, inventory databases, payroll from HR… it was cross-calculating KPIs across these datasets and showing them in one single view… oh and it was editable by the click of one of the many slicers in the dashboard.
Nachito showed me the magic of PowerPivot while warning me that “no one really learns it, they don’t listen”. But I listened; I realized that this could be one strategic advantage against more experienced consultants. And it was. And it still is. So I followed his recommendations to buy the Building Data Models with PowerPivot book.
With it, I explored what data models were and how could I start creating these database-like structures designed for business users.
There Is a Natural Progression from Old-School Excel Reports to Sleek Business Intelligence Tools
The video I made for you today outlines the progression from a traditional Excel report (Monthly Electricity Statistics from the International Energy Agency) towards a more advanced reporting tool based on PowerPivot with slicers, and finishing with an even more advanced dashboard in Power BI.
Without getting too close to the grimy details, I must say that the natural progression is easier than what you would expect. In Excel, the people in charge of building reports are often developing a “master database” or a “master table”, which pretty much means that they will use the data from two or more tables and with help of macros, VLOOKUP, INDEX, MATCH, Array formulas, or other sorts of Frankenstein mechanism will join together the data from all the tables into one huge table. And I mean huge… dozens of columns and thousands of rows. This is not a good solution.
However, this same need to put things together exists within the data model capabilities of PowerPivot and Power BI. Learning how to understand the filter context and the power of relationships between tables will literally eliminate the need for nasty Excel Frankensteins.
In further posts, I may expand further on this topic, which I believe applies for all of us working pretty much anywhere. We all are dealing with data, the problem is that it may not be recorded in good order, and furthermore, it may not be reported with the appropriate flexibility and usability.
Everything written here is a personal reflection and is by no means educational, financial or professional advice in any way.
Please feel free to cite and refer reliable sources in the comment section down below.