From Excel to Microsoft Power BI: What skills do data analysts need?
Microsoft’s Power BI has become the market-leading BI analysis and visualisations tool and it is continually being developed on a monthly cycle. In 2020, the Gartner Magic Quadrant marks the suite of tools as the leader ahead of traditional competitors.
Here at QA, our Power BI courses have been developed over many years, with Power BI Desktop taking centre stage as the primary tool to connect to data, transform, model, analyse and visualise the data. The overwhelming majority of our learners who we meet come to Power BI from Excel. Traditionally, that software has been the go-to solution for assembling our data, calculating with it, and then creating charts and PivotTables to create reports.
I am often asked by customers what skills our Excel users need to develop to use Microsoft Power BI successfully.
While there are transferrable skills between Excel and Power BI, anyone who needs to become competent and confident using Power BI will need to enhance those skills and add to them. Moving from raw data to an interactive report or dashboard – which not only contains analysis but also provides the tools for colleagues to carry out their own analysis – involves some defined steps. And each step has its own skills requirements.
Data analysts need skills in these areas:
Connecting to data sources
Power BI needs feeding. It must connect to data and ingest it so it can be processed. You need to understand what data is required, whether it is structured, semi-structured or unstructured. Identifying the raw materials will determine the best approach or queries to use. Microsoft has provided a wide range of connection tools and capabilities to make the process as easy as possible.
Transforming the data
Excel formats data to make it appear and behave as you need it to. While Power BI provides formatting, this is primarily for reports and dashboards. It is much closer to a true database environment and that means you will need to have a thorough knowledge of data types and how your data will need to be stored in Power BI.
Transformation also involves shaping the data using the Query Editor. With the Query Editor, Microsoft has provided a great tool to enable you to prepare data for analysis. All connections to data and transformations sit inside a query written in Query Language known as M. Every step in a query is written in the language, and although you don’t need to be a guru in it, you do need to be able to interpret it to use it successfully.
Measures and calculated items
M isn’t the only language found in Power BI. DAX or Data Analysis Expressions is a function-based language that is extremely powerful. It builds on a combination of Excel’s function categories together with an object-based approach introduced in Excel’s Power Pivot add-in. You will need DAX!
A good understanding of Excel’s aggregate functions is a great starting place, but DAX takes functions to a whole new level. You will need it to write the Measures for your KPIs, create Date tables to match your reporting year, and to create ad hoc subsets of the data to meet reporting requirements.
Visualising the data
Visualisations are not just charts. A better description would be graphs with charts being a subset of them. You are probably comfortable using a selection of charts in Excel. Columns, lines, pies? They are all in Power BI and pretty much look the same. They do not always behave in the same way though, with built-in interactions and filter capabilities. Microsoft’s built-in visualisations include maps, scatters, cards, slicers, gauges, powerful AI visuals, tables, bookmarks and KPIs. Moving from Excel to Power BI means you will need to become familiar with a large selection of visualisation types, features and design considerations to tell the data story. You will move from displaying data to displaying analysis of the data.
In summary
The journey from Excel to Microsoft Power BI is a natural one involving a combination of upskilling and learning new skills. As a business user with a background in Excel, the adoption of Power BI will make you look at data differently, handle the data differently, analyse it with different tools and create interactive reports and dashboards.
We can help you on that journey.
View our Microsoft authored courses here:
Click for more information on our QA-authored course Power BI Desktop for Business Users:
