You’ve started OR you’re in the middle of your Power BI journey and you’re confused as to where you should allocate your learning time and efforts
Should it be DAX? Understanding the visualizations? the M language? Power Query? Power Pivot?
SOOOO many keywords that appear when doing a simple online search, but WHAT is the core of everything inside of Power BI?
This article covers my thoughts on where you should primarily focus your efforts when learning Power BI – let’s find out what is the HEART of Power BI.
After spending more than 6 years using the toolset and going through various iterations and stages of what we now know as Power BI, these are my own thoughts and what I’ve found works best for the people that I’ve trained over the years.
The Main Components of Power BI
The Power BI that we know and love has a client authoring tool (called the Power BI Desktop) and a service-side part (the Power BI on the Cloud and / or the Power BI Report Server).
In this blog post we’ll focus on the client authoring tool and the components of it since it’s what most authors are trying to learn first.
This client-side tool has 3 main components that try to tackle 3 different stages which are:
- Data Preparation – uses a tool commonly known as Power Query (with the M language)
- Data Analysis – uses a tool formerly known as Power Pivot (with the DAX language)
- Data Visualization – this is the final stage and the one where you create your report with different visuals
The ones that get the most focus, because of their complexity, are the Data Preparation and the Data Analysis stages where you need to use a combination of the DAX and the M language.
Nevertheless, they work together to create something that is known as the Data Model, which is the heart of your Power BI file.
You can learn more about why Power BI is a model-based tool from this excellent article by Marco Russo.
What is a Data Model?
Power Query and Power Pivot, also inside of Power BI, are the tools that let you shape a Data Model.
See, a Data Model is the end result of everything that we create with both of those tools:
- Tables – shaped and loaded using Power Query or created with DAX
- Relationships – created with “Power Pivot”
- Calculated Columns – created with DAX
- Measures aka Calculated Fields – created with DAX
Power Query helps you with shaping the tables which is one, if not, the MOST crucial part of creating a Data Model.
Why Power Query and not just Power Pivot and DAX?
Here’s a little back story. Power Pivot and DAX came out in 2010 and back then Power Query or the Data Preparation Layer was not around.
People who had SQL and SSIS skills could deal with this fairly simple, because they could shape the data inside of a database or with other tools, but us (Excel folks) didn’t have any proper tools at hand to deal with Data Preparation tasks and those scenarios created SO many situations where the optimized Data Model was simply not feasible for most regular Excel folks.
A Data Model created with nothing but DAX and Power Pivot
As an Excel user, If you worked with Power Pivot back in 2010, 2011 or 2012, you most likely saw a Table like the following in the wild loaded into your Data Model:
loading that table, in that shape, to a Data Model complicated things to a completely new dimension.
Of course, there’s always a way to MacGyver your way into shaping your data correctly either doing it manually, using a 3rd party tool or simply involving your IT folks to make it happen, and that’s what most people ended up doing back then, but it completely lost the purpose of self-service, because you ended up having a bottleneck at the IT department trying to fulfill multiple requests at the same time.
But if you don’t have any of that, you can imagine that the next time you refresh that table, it’ll have more columns as new dates get added, or perhaps some column names get replaced, which is just terrible.
This really felt like we had an amazing tool that we couldn’t fully leverage because our data wasn’t in the right shape / form.
Kinda like if we were in some sort of Stone or Dark Age of Data Preparation:
(how your Data Model felt like without proper Data Preparation – not the best “tires”)
How Power Query changed the gameYou can see where I’m going with this analogy.
Having the proper set of tires on your car (your Data Model) makes a HUGE impact on the performance of your vehicle and the journey that both the driver and the vehicle travel.
Power Query came as the tool to shape the tire to every extent that you can possibly image in order to have the best tire for your car.
Following up to the previous table that we had, imagine that with just a few clicks, we can “Unpivot” those columns and shape our table this way:
where we have transformed or reshaped the table to have the dates in one column and the values for each date and account on another column which is the best way to shape data for a Power BI Data Model.
This is just one of the transformations or Data Preparations that Power Query brings to the table and one of the many things that you can’t do with Power Pivt / DAX, but you can do with Power Query. They essentially tackle 2 different tasks, and Power Query is your new bestfriend when it comes to Data Preparation.
The most important thing in Power BI: Data Modelling
in the most simple terms, imagine that with Data Preparation you can shape your Data in the most optimal way for its usage in Power BI. It’s like using the best tires for your travel – where the tires are your tables.
And the best self-service Data Preparation tool for Excel users is Power Query which is what we now know as the Get Data experience inside of Power BI.
If you’re trying to learn Power BI, then the best place to start with would be to understand Data Modelling and have clear concepts of what the Star Schema is. Then you can go with Power Query to shape your data to fit that Star Schema and ultimately learn DAX.
One important aspect of good Data Modelling is that if it’s done correctly, then the DAX part should be pretty straightforward and easy.
Without good Data Modelling, you’ll find out that it’s a pretty tedious task and you might end up down the DAX rabbit hole trying to figure out why the performance of your model is so slow.
Excel specialist turned into BI specialist using the latest tools from Microsoft for BI – Power BI. He is the co-author of ‘M is for Data Monkey’, blogger and also Youtuber of powerful Excel video Tricks.
He has been recognized as a Microsoft Most Valuable Professional (MVP), is a Microsoft Certified Professional (MCP – MCSA: BI Reporting), a Microsoft Certified Trainer (MCT), and is one of the international pioneers in Power Pivot, Power Query and Power BI.