I love the Power BI community. There are so many great things that the community does. They help each other (think the Power BI community forum) and they also build fabulous third party external tools to enhance Power BI.
Measure Killer
This week I am sharing a brand new third party Power BI external tool called Measure Killer created by Gregor Brunner. At this writing, the tool is still in beta, so make sure you backup your Power BI workbooks before using this tool. In fact, that is good advice for all Power BI authoring, regardless of what you are doing.
Versions of Measure Killer
There are 3 options currently available.
- There is an MSI that requires Admin rights.
- There is a portable version that does not require admin rights and can be installed on a USB.
- There is a version on the Microsoft Store that requires admin rights.
I installed mine from the Microsoft Store so that it automatically stays up to date with the latest version.
It’s Built for Import Mode
It is worth pointing out that Measure Killer works by connecting to the meta data in a PBIX file. PBI workbooks are XML files containing all the information needed to render a report. More correctly, an Import Mode workbook contains all the data. If you are using a thin workbook where the model is elsewhere (either live connect or another PBIX), then Measure Killer will not be able to validate either the measures, or the measure usage in visuals. Measure Killer is used for Import mode PBIX files.
Launch from the External Tool bar
Assuming you have installed one of the versions that requires Admin Rights, you can launch from within Power BI from the external tool bar.
Select the file and run
Because I am launching from within PBID, I clicked “select file” (1) then Run (2). You could also choose a different file from this dialog if needed.
List of Results
After running Measure Killer, I was given a list of objects (Measures, Columns) that were targets to be deleted because they were unused in the model.
Note the 2 red buttons above. One provides a C# Script to kill the measures and the other provides the M Code to remove the columns. I also opted to save the results in an Excel file. The Excel file provides rich information about every measure and column in the model including if it is used, and if so, where is it used.
Kill the Measures
In my testing, I generated the C# Script and then pasted it into Tabular Editor 3.
After deleting the measures, I ran Measure Killer again, and it gave me a much smaller list of measures to delete. My guess is these were dependent measures and could be deleted once the up stream measures were deleted in the previous run. I ran Measure Killer 3 times to get rid of all unused measures.
M Code to Kill Columns
The approach to removing columns is a bit different. From what I can see, Measure Killer copies the current Power Query code for each table, adds a new step to remove the unused columns and provides the entire advanced code to you. You can then cut and paste that code back into Power Query to replace the existing code.
Note the before (1) and after (2) code below.
What do you think?
The tool is still in Beta, so there is more work to be done. Having said that, I love it. Others have done something similar to this in the past (notably Imke Feldmann) however all previous attempts I have seen require a lot more manual intervention to get everything set up. Measure Killer makes a significant step forward in automating that process. I hope at some stage that it will be able to execute the C# and M code itself, further streamlining the process.
So what do you think?
Be the first to comment