Power Query is used to prepare each of the tables loaded into the data model. Hence, it’s fundamental that the tables, fields, and measures in the data model should be uncomplicated and user-friendly. In this tutorial, let’s talk about some Power Query best practices for our data model, some of its features, and why we should use the query editor.Â
Power Query allows users to do very complex stuff. Therefore, it’s always important to follow a couple of best practice rules to keep everything properly organized.
1. Always Bring Data To The Power Query Editor
People might usually import their data directly to their data model by using the Get data option.
I highly suggest you do not do that and use the Query Editor first. This means that we should always bring our data to the query editor to clean them first. The reason is because data is never perfect. It would be better to check the data in the Query Editor before adding it to our data model.
2. It’s Always A Best Practice To Understand What A Query Is
One of the most important Power Query best practices that I’d recommend is understanding what a query is.
A query is like a snapshot of our data in its worst form. It doesn’t physically transfer anything into our Power BI model as well.
Since our data tables could be big, we want to query it and don’t create any overload in terms of our Power BI models. Once we get them in the query format, that’s when we do all the cleaning and transforming of those tables. Therefore, it’s crucial to have a good understanding of what a query is versus directly committing data in the data model.
3. Practice Organizing Your Queries For Model Development
It’s so important in terms of Power Query best practices for model development to organize our queries. This is because we’ll have a lot of queries when we develop more and more inside Power BI. Sometimes, a query could be like a staging table, and eventually might get appended or merged into another table. So, we might get a lot of queries and we need to be able to manage them.
In this example, I organized them on the left hand side using folders. We can also drag and drop our queries to put them in a certain order. The key thing when organizing them is to name them intuitively as well—not only the queries but also the folders that they sit in.
4. Analyze The M Code In The Power Query Editor
The other Power Query best practice that we need to learn is to know what goes on inside the Advanced Editor and more specifically, with M code.
This is an example of a detailed M code with the dates query. It’s simply a code that will change every time we make a transformation. So, it just lays out all the different details of transformations we’re doing.
For example, let’s remove a column here.
Then, if we open our code on the Advanced Editor, we’ll see that it also recorded that we removed a column. We don’t need to write the code but we do need to have a good understanding of the code. Once we’re able to analyze it clearly, we can see that there’s quite a few opportunities to do a lot more advanced stuff in this part.
5. Structure And Optimize Power BI Tables
Lastly, I highly suggest users to have an understanding of how we want to structure or optimize tables for Power BI. This is really crucial because at the end of the day, once we get past this query stage, we’re going to commit it to our data model and have to build a data model around it. We’ve got to have the data model in mind as we’re working through this, because this is where we are optimizing your tables for the data model.
So, what is a good shape or what is the most optimal shape for our tables to fit inside our data model? There’s no actual exact answer to that as well because every data situation is unique in a lot of cases.
***** Related Links *****
Power BI Query Parameters: Optimizing Tables
Unpivot And Pivot Basics In Power BI – Query Editor Review
How To Use Power Query Row And Column Selection
Conclusion
So, those are my suggested Power Query best practices and some of the main key things that we’re going to cover in the other blog articles. Following these general tips can help you prepare a proper data model which is considered as the heart of a Power BI report solution.
Always keep in mind that it’s really essential to have an understanding of what’s going on inside the Query Editor. From there, we can go and apply what a good and optimized table looks like into our own data scenario and into our own model.
All the best,
Sam
