This tutorial showcases some advanced tips to optimize your Power BI table. I’ll show you how to break out your large table into multiple ones. This concern is quite common across legal database systems with huge flat files of information. You may watch the full video of this tutorial at the bottom of this blog.
Huge tables need to be broken down into simpler tables for easy information management.
Most importantly, inside Power BI, these huge tables also need to be optimized to create a structured model. Power BI tables are designed to tone down huge flat files.
This discussion is from a particular request in the Enterprise DNA Support Forum. A member wanted to break down a large database table from the SQL server. An Enterprise DNA expert provided a great solution for the said situation.
I want to dive into this example and help you understand more about the things that you can possibly apply to your own models.
Transforming The Data Using Power BI Query Editor
This is what the initial Sales table looks like. It came from a very simple sales scenario, but it’s still a massive table.
You need to clean this table by using the best practice tips for managing Power BI tables. You can also extract some key lookup columns that relate to each other because these tables can be useful when you want to create your own table.
However, optimizing this kind of Power BI table can’t usually be solved in the front end. You need to use the Power BI Query Editor to transform this data.
To do that, go to Transform Data and make the changes there.
Changing A Column Into A Lookup Table
The example that I want to work on and show you involves changing the Channel column into a lookup table in the model.
Instead of having all the repeated names under the Channel column, you can break it out and add other details.
There are many ways to group the data by using different abbreviations and combinations. There are also various ways to filter the calculations with another lookup table.
First, you need to grab the fact table query. This is just a query so there’s no actual data that’s being brought into the model.
Right-click the existing big table. In this case, it’s the Sales table. After that, select Reference.
Change the name of the copied query to Channels.
Highlight the Channel column in the table, right-click, and then select Remove Other Columns.
After that, you can generate a brand new table that only contains the Channel column.
Then, right-click and select Remove Duplicates.
Finally, the new table will only have three entries under the Channel column.
Creating Abbreviations And Index
Another thing that you can do to optimize your Power BI table is to change the names and create abbreviations.
To do that, go to Add Column, and then select Column From Examples.
You can rename and create different abbreviations for the existing data in the column. For instance, you create a new column and name it as Channel Code. Under the said column, you can add abbreviations like WHOL, DIST, and EXPO.
If you want to deeply optimize the Power BI tables, you can also create an index column and create another one in the main table.
For example, in the Customer Name Index column, you can just use numbers or letters like 123 or ABC.
This is a bit technical, but doing this technique gets rid of too much text and saves memory upon transferring this data into your Power BI table. If you do this, the table can be saved in a smaller byte, thus the model won’t be too big.
Removing Columns To Optimize Power BI Table
You can even take this further by organizing the columns inside your Power BI table. If you have a longer list, you can group them in a slightly different way based on their name and use a SWITCH statement. There are many ways to create an entire model based on one table.
You can also break out a table especially when you have lots of currencies. Again, just right-click, and then select Reference.
If you have multiple columns that are related, you just have to remove other columns to quickly generate a new table.
The most important thing that you should remember when you extract the information from the large table is to freely remove columns as well. You don’t need to have all the data in your fact table and everything else in your lookup table. The fact table should be as thin as possible and you should create many lookup tables to consolidate your data as much as possible.
***** Related Links*****
Creating Dynamic Ranking Tables Using RANKX In Power BI
Master Virtual Tables in Power BI Using DAX
Creating Power BI Tables By Using UNION & ROW Function
Conclusion
I’ve shared useful techniques here in optimizing your Power BI tables. These concerns are quite common especially when you come from an Excel background. I often see a lot of concerns about huge flat files since there could be thousands of rows of data from the start.
What you really need to do is create a model around it and have a thin table. When you filter accordingly, you can have a more condensed table that has all the necessary information like region, customer products, and more.
Enjoy this one!
Sam
