When we import data into Power BI, we may sometimes import columns that we don't actually need. These redundant columns can bloat the size of our data model, making it slower to load and refresh.
Additionally, when we're developing Power BI reports, we may create measures that we only use for testing purposes. These measures may never actually be used in a published report, or they may not be used in the calculation of other measures.
Both of these scenarios can lead to unnecessary bloat in our data model.
Recently, I was looking into a similar issue to identify unused columns and measures and delete those artifacts and come in contact with an amazing tool by Brunner bi the Measure killer
Let me explain how it works.
First, you need to download the software. It can be downloaded by clicking here or you can download it directly from the Microsoft Store. Once downloaded and installed, it will be in your external tools tab
click on it, then in the measure killer app select your file from the dropdown and click on run
once the process completes, it will show all columns and measures in the report with some additional details like the size of the column and table in which the column resides
Red rows are unused columns/measures. If you want a comparison of used versus unused. simply click on plot results from the ribbon and select plot unused vs used
This column chart will draw a comparison of unused and used columns/measures
Before deleting any measure and column, it is recommended to save result as if by mistake you delete a measure, the report will contain the expression which can be easily copied
Remove column
To kill columns, click on kill measures and columns and then select kill measures
A new pop-up window will open, here
Select the mode. which is to remove columns
Select the table, App will fetch unused columns from the table and will also indicate the combined size of these columns
Click on apply. The app will generate an M-Code
Copy M-Code
Open your BI file and click on Transform data
select the table for which the app generated the M-code
click on the advanced editor
Replace the query with the M-Code generated by measure killer and click done
Remove Measure
There are a couple of ways to remove measures.
Kill all unused measures
kill selected measure
Generate C# script for the tabular editor
The first two options are really simple you just click on them, and the app will delete the measure. Save the BI file and close it, when you open the file again, those measures will not be there
For the C# script, select the option, and the app will create a C# script and copy it to the clipboard.
Open the tabular editor, select C# script, and paste the script there. then click on run
once the query is executed, click ctrl+S to save changes back to the model. Now open the BI file and the measure will not be there
So, this tool can help to save lots of time for developers to manage data models.
Comments