Miguel Escobar Published February 5, 2019

The most important thing to learn when using Power BI

Power BIPower Query

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

image

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:

image

  • 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?

image

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:

image

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:

wheel

(how your Data Model felt like without proper Data Preparation ā€“ not the best ā€œtiresā€)

How Power Query changed the game

Tire free iconYou 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:

SNAGHTMLb7f2511

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.

Power BIPower Query
Subscribe
Notify of
guest
7 Comentario
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Christopher Majka

Excellent overview – thanks!

glad you liked it! šŸ™‚

osiel

Genial!!

Great blog post, everyone should read this!

Afonso

Honestly? Power query, power pivot (so, consequently Power BI) are a piece of ***. You query data, you perform some steps, and display the data. It sounds great, but in practice, the steps to transform the data will break ALL THE TIME. In order to repair, you have to go through the steps, it means, the data is queried and loaded ALL THE TIME you change the steps, it takes AGES to repair something… At the end of the day, something that was supposed to increase of productivity is sooooo fragile that break all the time and DECREASE your productivity. I’ve been using it for about 6 months, every day, and gave up now! Learn how to program instead of learning this ***.

Jonathan

I would have loved to hear more from Alfonso’s frustrations..On the contrary, these tools are designed to cater for non programmers. Everyone does not need to learn programming to be productive. These self service BI tools have really helped me become productive in a short space of time…