After having an extensive discussion about the query editor, it’s time to move all that data into a Power BI report through data modeling. You may watch the full video of this tutorial at the bottom of this blog.
Data modeling allows you to connect different data tables in your Power BI report by creating relationships between them.
Fixing Relationships In The Core Data Model
My core data model consists of all the tables I fixed in the query editor. This includes my Customers table, Locations table, Products table, etc.
Since I’ve cleaned up those tables and I’m sure that I have all the data I need for now, I’m going to click on Close & Apply to close the query editor and apply any pending changes.
Of course, this doesn’t mean that I won’t be going back into the query editor once I start working on data modeling. I would definitely be coming back into it a lot, especially if I need to make additional transformations or if I need to bring in new data depending on what my reports require.
The process of applying the queries into my Power BI model might take some time, depending on the amount of data I have.
But once the application is done, I’m going to end up on a fresh canvas where I can start creating my analysis.
Some people may jump straight into this part. But what I usually do is check the relationships built around the data I worked on when I was in the query editor.
This is the modeling area, which I can access through this particular icon here.
If you have no Power BI experience and were previously working on Excel, this will be completely unfamiliar to you. It’s important that you understand this part because it is crucial in developing anything within Power BI.
This is where I can make sure that the relationships across my data are set up in an optimal manner. Otherwise, it will be harder to get any calculation or analysis done correctly.
The relationships are represented by these lines and arrows running across the data tables.
Power BI normally guesses the relationships among the different tables automatically. Unfortunately, Power BI gets this part wrong most of the time.
So I usually start off by deleting these existing relationships, especially when I’m 100% unsure if the default relationships Power BI has placed make sense.
In Excel, you would normally have one huge file with hundreds of columns. But data modeling within Power BI is different.
Here, for example, I have a core set of information, and then I have separate supporting tables that have filtering information within them. So I end up with different tables with different data groups in each one.
This is the critical part in data modeling – identifying whether each table is a lookup table or a fact table. It’s important to understand what these two types of tables do so that you can understand what kind of relationships you can draw through them.
A fact table carries all the transactions, like in this sales table.
On the other hand, a lookup table carries filtering information.
Once I’ve identified where my tables belong, I organize them. I like lining up my lookup tables here on top in a single row.
As for my fact table, I like putting it below.
Of course, some people might do it differently. But this is the best practice I want to share because I have a philosophy of keeping things as simple as possible.
I layer these tables like this because I want to visualize how the relationships go. I call this a waterfall of filters. This way, I have my lookup tables on top sending filtering information down into the raw data that I’m working with.
***** Related Links *****
Top 3 Best Practices To Organize Your Power BI Models
How To Organize Your Power BI Data Model – Best Practice Tips
Customer Segmentation Techniques Using The Data Model – Power BI & DAX
Conclusion
This is very basic stuff, but it’s crucial that you master this because it can make or break your calculations later on.
Understanding the basics of data modeling helps you make sure you’re getting the right results in your reports. It will save you the frustration of trying to find the root cause of errors that might result from the wrong relationships.
All the best,
Sam
