My Top 3 Power BI Data Modelling Tips

Power BI is a powerful tool for data analysis and visualisation. One of the most important aspects of Power BI is data modelling. Data modelling is the process of organising and structuring data in a way that makes it easy to understand and analyse. While there are many different ways to model data, in my experience from building data models and reports, some ways are more efficient than others in preparing your data model. So, I would like to share my top three tips when it comes to preparing your data models in Power BI: 

Use the properties pane in the Model view when updating multiple column formats. 

When I first started my Power BI report building journey, I used to update a column’s data format individually. I found this process to be arduous; waiting for Power BI to individually process each change sequentially. Instead, I recommend using the properties pane in the Model view to efficiently update multiple columns of the same type of data at once. 

The properties pane in the Model view provides a convenient way to update the format of multiple columns at once. To use the properties pane, first select the columns that you want to update from the Data pane (you can do this by holding CTRL on your keyboard and clicking on the relevant columns). Then, click the "Properties" button in the toolbar. In the properties pane, you can change the data type, format, and other properties of the columns. 

For example, you could use the properties pane to change the format of all the date columns in your data model efficiently to a consistent format rather than click through each date column one by one and change the data type from within the Column tools tab. Once you try this, you won’t go back to the old ways! 

Use the Layout feature in Model view. 

As a MWM (Modern Work Management) consultant, we typically deal in the realms of bringing together Project data from a PPM system (e.g. Project Online, Project for the web) which means we typically have a star schema with lots of data tables representing different facets of our project data. This is where the Layout feature in the Model view is particularly handy. 

The Layout feature in Model view allows you to rearrange the tables and columns in your data model. This can be useful for improving the readability and usability of your data model. To add a new Layout page, click the ‘+’ button on the bottom ribbon next to the default layout view (All tables). This should provide you a blank canvas where you can drag and drop the tables from the Data pane and rearrange them on the page in your desired order.  

In my example, I use the Layout feature to group risks and issues together with the Projects table to view the relationships between the three tables. In other cases, you could use the Layout feature to create new layouts which group other related tables together (e.g. Benefits, Resources, Dependencies). This makes it far easier to find and visualise your data model and their relationships when you are creating reports and visualisations. 

Use the Properties pane in the Model view to edit existing relationships. 

I only discovered this one recently actually! I used to manage existing table relationships with one hand pointing at the screen, another hand on the mouse and my pair of eyes guiding my way through a jungle of lines and tables like a monotonous game of dot-to-dot (except it was table-to-table) in order to find which table relates to which. Avoid all this, by using the properties pane in your model view instead. 

The Properties pane in the Model view provides a convenient way to edit existing relationships between tables (e.g. from a one-to-many to a many-to-many). To use the properties pane, first select the relationship that you want to edit. Then, click the "Properties" button in the toolbar. In the properties pane, you can change the type of relationship, the direction of the relationship, and other properties of the relationship. 

Additionally, if you select multiple relationships (by holding CTRL and clicking on more than one relationship), you can activate or inactivate multiple relationships at once  

These are just a few tips for data modelling in Power BI. By following these tips, I hope you can create data models with greater efficiency that are easy to understand and use.