Edit: I am retiring support for this tool for now, as you can find an alternative here: Measure Killer | Brunner BI.
Please feel free to use this tool as it is, but I am not fixing any bugs or provide further help for it. Power BI Cleaner is now published with ‘Power BI Sidetools’ by Didier Terrien: https://github.com/didierterrien/Power-BI-Sidetools/releases Didier may provide help in some cases. Please post issues here: https://github.com/didierterrien/Power-BI-Sidetools/issues
Edit 23rd August 2021: This article is outdated, please read the updated version here: Power BI Cleaner Gen2 now covers Analysis Services Models as well
Today I’m very excited to share with you my first version of a complete rework of my Power BI Cleaner tool. It is way faster the the initial version, overcomes some bugs and limitations of the old version and doesn’t require creating additional vpax files.
On top of that, I’ve created an Excel-version, that adds some very convenient additional features: The option to analyze thin reports and to generate scripts that delete unused measures and hides unused columns automatically.
Basic Power BI version
Fill in parameters
First you have to open the Power BI file whose data model you want to analyse. Then open the Cleaner Power BI template file and fill in the local port number and file path of the file to analyze into the 2 mandatory parameters.
Enter parameters for the new Power BI Cleaner file
I’m always using DAX Studio to grab the port number, but there are other methods described in this article as well. Please use only the numeric part (not “localhost”):
Retrieve port number from DAX Studio
When you try to refresh the data, you will be prompted with a dialogue about how to connect to your local Power BI model:
Connect to data model that runs on a local Analysis Services instance
Just accept the default dialogue (“Use my current credentials”).
Next comes a prompt for Privacy Levels. Click on “Ignore Privacy Levels” in there:
Ignore Privacy levels in this prompt
Main report
Main report
On the Overview page, you see a matrix with table and column names in the row section and usage information in the columns. Upon readers requests I’m distinguishing between fields that are not used at all in the report (1) and those who are used in DAX expressions, but the fields it has been used in, have not been used in the report at the end (2). So fields in here can also be deleted from the model. But depending on when you delete them, you might break some measures or calculated column temporarily. But those are the ones that can also be deleted at the end. The decision to delete a column in the source or hide it in the report might depend on the column size, which is also given at the end of the matrix (5).
In the next column you see where the used fields are actually used (3). So these fields should stay in the model. The next column is just a summary, indicating if the field can be deleted or not.
To the left there are some filters that you might find useful. For example, you can select all fields that can be deleted by choosing the first box in the “Can be deleted”-filter (6).
If you expand the fields in the row section, you will first see the DAX expression of the field. After that, you see where this field has been used throughout the report.
The last 4 pages of the Excel file (DMV_Measures, DMV_Columns, DMV_CalculationGroupItems, DMV_Hierarchies), just hold a list of all DAX expressions in your model as a reference.
Delete or hide unused items automatically: Excel version
The Excel-version can also generate scripts for you that can delete unused measures or hide unused columns automatically. It also allows to analyze thin reports: Meaning Power BI reports that don’t have an own data model but instead use a live connection to a central data model in the service.
To start you have to fill in the port number into B9 just like in the Power BI version. In B12, you must fill in the path to the pbix like in the Power BI version or the path to a pbit version. Both will work.
Click on “Refresh main result” will refresh the data and move you over to the “Result” page. This looks just the same than in the Power BI version.
The next page “Measures_Delete” holds a table to the left with one row for each measure that is not used. Based on it, the table to the right will generate a code that can be used to delete all the measures using the Tabular Editor tool.
Delete unused Measues
Before using this functionality, I strongly recommend to make a copy of your Power BI report, as the following actions are not officially supported by Microsoft. So if they break your report, they will not help you out on it.
The left table contains a list of detected measures that are not used anywhere in the report. If you want to keep some of the unused measures from there, simply delete the “Delete”-entry in column “Action” and it will be excluded from the code. Once you’re happy with the selection, click the button “Create and copy code”. This will refresh the table to the right that contains the code that would automatically delete all the listed measures if used in Tabular Editor.
Use Tabular Editor to delete or hide unused items
Then you can simply copy the code, open Tabular Editor (LinkToProVersion, LinkToFreeVersion) and paste the code into the “Advanced Scripting” window. Click run, save the file and the cleanup is done:
Open Tabular Editor
Connect to PBIX model
Select default settings
Paste code from Excel and run script
Save modifications to the model
In your PBIX-model, the measures should be gone now.
The same principle goes for the columns, that can be hidden with the tables from sheet “Columns_Hide”.
Analyze thin reports
Based on files
Also in the Excel-version, there is a table where you can add all the files to analyze that are using the currently opened dataset. Yes: You can analyze “thin reports” with it that don’t have an own data model, but are connected to a centrally published model instead. Simply add the path to the pbix or pbit-versions into table “ReportFiles” on the “Instructions”-sheet. The model that holds the central dataset must be open in Power BI Desktop and its port number entered into B11 of the “Instructions” page.
In the pivot table on the “Result”-sheet, the last column level field is “Report File(s)” and there you can see in which (thin) report each element is used.
Directly from the service
And to make it even more convenient, you can also analyze your reports from the Power BI service directly. This requires an app registration that provides the access to this data. This article provides a guide on how to register such an app. The app needs to have “Workspace.ReadAll” and “Report.ReadAll” permissions. To authenticate with this service from Excel, you can create and grab a token from one of the API endpoint-pages, like: Datasets – Get Datasets – REST API (Power BI Power BI REST APIs) | Microsoft Docs. There you must click on “Try it”:
Connect to endpoint to grab token
Next the authentication will start and you have to login to the account that has access to the app. On success, you’ll see a window like so that displays the token. Click on “Copy”:
Copy token from webpage
Paste it into B7/B8 of the Excel sheet.
Paste token details into
Also fill in the dataset ID into cell B2. You find the dataset ID in the URL if you edit the dataset details in the service like so for example:
Grab shared dataset ID from URL
2 different connection modes
If you have admin rights in Power BI, the queries can fetch details from all workspaces in your tenant. Therefore you have to switch the value in cell B4 to “As Admin”. Otherwise, keep the default entry “As User”. In that case, only the details of those reports will be displayed, where you have access to.
Please note, that even measures that have been created in the thin reports (report level measures) will be detected as well. Just have in mind, that they will not be covered by the automatic
As long as there is an entry for the DatasetID in cell B2, Excel will try to use the dataset and the corresponding token. After a while, the token will become invalid. To generate a new one, simply refresh the page where you’ve grabbed it from. If you haven’t already, now it’s time to vote to enable the usage of custom connectors in Excel so this cumbersome process can be omitted: Add support for custom data connectors (Power Query M extensions) – Welcome to Excel’s Suggestion Box! (uservoice.com)
This has been a frequent request by many users and I’m really happy for being able to provide this now. Just don’t forget please, that this analysis doesn’t include fields used in Excel-reports that are connected to these central datasets.
Areas covered
The following areas of field usage are covered in these versions:
- DAX expressions (measures, columns, hierarchies, tables, report level measures, roles, bins, calculation items and title expressions)
- Incremental refresh policies
- Relationships
- Filters (visual-, page- and report level)
- Visuals (incl. conditional formatting)
Limitations
- When upgrading your workspace, your reports might be rebound to the dataset. In that case, the API will not return any data.
- Automatic retrieval by the API will also not work for reports in the old workspace format.
Warning
I cannot guarantee that this all works 100% correct, so you’re using it on your own risk. Don’t forget to make a copy of your file before starting to throw out your measures and columns. If you find any bugs, please mention them in the comments.
Feature requests
Are there additional functionalities that you’d like to see in this tool?
Post it in the comments, please.
Problems
If you’re getting a warning that data sources cannot be combined, you must disable privacy settings like so:
Disable Privacy Levels in Excel
Download files
Attention: These downloads belong to the current version of the tool. Please read this blogpost for up to date instructions.
PBIT: Power BI Version Cleaner Gen2
Excel version with macros: Cleaner Gen2 Excel-Version with Macros
Enjoy and stay queryious 😉
Thank for your job! I hope it is a minor issue.
I got Server address from Dax studio and then got an error:
“AnalysisServices: A connection cannot be made. Ensure that the server is running.”
Hi Stepanov,
not sure what you mean with “Server address”, but it must be the numeric value of the port number.
I’ve updated the article to make this more clear, hopefully.
Thanks for pointing this out.
Cheers, Imke
Hello Imke, that looks great !
I get the following error with Excel 2013. Are there some pre requisites like Power Pivot ?
Sorry… We couldn’t refresh “Query StorageSize” :
Query “fnPortModel” (step “Source”) reference other queries or steps and so cannot access directly to a data source
I didn’t find this “fnPortModel” in my queries
Thanks for the tool ! It seems to be quite exhaustive
Sorry for my previous comment. After I updated Power Query and I ignored privacy level in Excel’s Power Query, the error message disapeared but then I got an other one :
We couldn’t refresh « StorageSize » table from connexion « Query – StorageSize » :
MdxScript(Model) (6, 54) Syntax for ‘CanBeDeleted’ is incorrect.
That’s strange as the refresh works in Power Query
Hm, yes, that’s strange.
Probably an issue with the macro then.
Have you tried refreshing this query without the macro?
Pingback: Power BI Cleaner Gen2 – Curated SQL
Yes I tried refreshing this query without the macro. It looks like working as I see the table and Load status is “Loaded to data model” but there is still the error. It might come from my setup.
I noticed there is one more step (added index) in the Excel file query “AllFields_Used”. I removed it.
With the pbit :
You may add the desktop version needed for your pbit file as a pre requisite. I needed to update because April version returned an error.
First I got these errors but I refreshed and then it worked (might be a precedence issue) :
AllFields_DAX
Query ‘AllFields_DAX’ (step ‘Added Custom’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
AllFields_Used
Query ‘DMV_Columns’ (step ‘Added Custom’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Thank you, Didier.
These errors usually come from the security settings and once the Privacy Levels are disabled, they should go away.
Sorry about the version problem, but I don’t run older versions on my machines and it’s difficult to estimate which older versions will break.
/Imke
Hi .
It is coming with en error while trying to upload the data in all 4 of them:
We cannot convert the value null to type Text
Me again.
It seems like in case of direct connection from the report to an AAS model you report cannot handel it.
Hi Yair,
oh yes, I should have mentioned that. Sorry.
I believe I can adjust it to handle AS models as well. Let me check this out in the evening.
Hang in there 😉
Cheers, Imke
That will be great with AAS.. tnx for you effort 🙂
Hi Yair,
due to other commitments, this will have to wait until the weekend, unfortunately.
Cheers, Imke
Hi Yair,
we’re finally there: https://www.thebiccountant.com/2021/08/23/power-bi-cleaner-gen2-now-covers-analysis-services-models-as-well/
Please let me know if that works for you.
Thanks, Imke
Hi,
Love your tool, super easy to use and very simple to read and analyse from this. It has also made my job of cataloguing all of our measures and column naming so much quicker.
I have only run it against a few of my reports but spotted one possible improvement. I noticed that your tool stated my column was not being used at all when it was in use in the smart narrative box. Not sure if its something local on my end or if it is even something your are able to do.
Thanks for taking the time releasing and improving this tool,
Tom
Thanks for reporting Tom,
I will follow this up.
Cheers, Imke
Hi Tom,
I’ve included usage of field in smart narrative visual in the current version here: https://www.thebiccountant.com/2021/08/23/power-bi-cleaner-gen2-now-covers-analysis-services-models-as-well/
Hope it works for you.
Cheers, Imke
Hi Imke
I have tried your cool tool, but I have run in to a problem.
It might be a bug, or I might have misunderstood something.
In my .pbix I have two measures. Both is used. But the Power BI Cleaner says one of them can be deleted as it is not used at all. In reality it is used in a number of visuals.
I just thought you should know, and if you could guide me in the right direction, that would be great.
Thank you!
Regards
Mette Axelsen
Hi Mette,
thanks a lot for reporting this.
Are you able to share the pbit of your file for me to troubleshoot?
Thanks and cheers,
Imke
This is a super-useful tool ! I just used it for the first time and it worked like a charm. If I find something to work on, I’ll post it, but for now it’s wonderful. Thanks for the hard work and for making my life easier ! Cheers
Hi Imke
Thanks for sharing this tool! I have a question before having a go with it.
My goal is to streamline the impact assessment and testing process when working with datasets that support multiple reports. So for example, when I update the DAX in a measure in a central, ‘golden’ dataset, I’d like to check the reports using that specific measure (in a filter or visual). We have reports across multiple workspaces, so I’d want – in an automated way – to identify which ones to include in testing. Of course, Power BI shows ALL of the reports using a dataset but this is less efficient than knowing only those affected by changes to a specific measure.
Could your tool help with this?
Thanks!
Charlie
Hi Charlie,
if my understanding is correct, there is no shortcut to it other than my approach:
First get all reports that use the dataset and then check for measures in them.
/Imke
Thanks Imke! Appreciate you responding on top of making this tool and sharing it with us.
Will have a go.
Hi Imke. Thanks for a great tool.
I have encountered an error from the function call:
fnAllTextBetweenDelimiters([FilterExpression], “[“, “]”)
Namely when the input does not contain any of the delimiters.
Error message:
An error occurred in the ‘fnAllTextBetweenDelimiters’ query. At least one of the delimiters is not found in the string
The error occured in the named Power Query “DMV_U_Roles” where a Row-Level security filter expression is just the expression FALSE().
My solution was to change the M-expression where the function was used:
Before:
= Table.AddColumn(
#”Expanded DMV_Tables”,
“Columns”,
each fnAllTextBetweenDelimiters([FilterExpression], “[“, “]”)
)
After:
= Table.AddColumn(
#”Expanded DMV_Tables”,
“Columns”,
each try fnAllTextBetweenDelimiters([FilterExpression], “[“, “]”) otherwise null
)
Thank you Claus!
Will integrate this fix in the next version.
/Imke
Hi there, this is a wonderful tool, it can really leverage productivity and performance while developing in Power BI. Thanks a lot for sharing this great work!
I just want to report a possible issue with the Excel version. I am trying to connect it to a Power BI Desktop file which has a Direct Query connection to an Azure Analysis Services Tabular model and am receiving the following error message.
Microsoft Visual Basic
Run-time error ‘1004’:
We couldn’t refresh the connection ‘Query – StorageSize’. Here’s the error message we got:
Query ‘fnProtModel’ (step ‘Source’) references other queries or steps, so it may not be directly access a data source
I don’t know if my scenario is even supported by the tool, please advise.
Hi Mau,
please check the last section of the article under “Problems”: You should check “Ignore privacy levels”.
Live connection to SSAS is supported.
/Imke
Hi Imke,
This is exactly what I need. Super cool tool!
I downloaded the newest version but I still got an issue that there is no value in “Where Used” even though I have already ignored privacy notes and refresh all. I’m not sure what the cause about it. It would be very helpful if you can provide some ideas or measures for me to fix it!
Thanks a lot.
Hi Sakae,
I will publish a new version next weekend, hopefully it will work then for you.
Sorry for the inconvenience.
/Imke
Hi Sakae_Aletta,
I’ve published a new version here: https://www.thebiccountant.com/2021/08/23/power-bi-cleaner-gen2-now-covers-analysis-services-models-as-well/
Hope that will fix your problem?
Cheers, Imke
Hi Imke.
I have tried your latest version….is it me or i can not use it with AAS?
It is still writing localhos and not the AAS url i gave it plus it is asking still for port number… i dont have on as i am using AAS.
Would love to hear from you about it
well done with you work and gret efforts 🙂
Tnx in advance
Yair
Hi Yair,
sorry to hear.
Cannot reproduce you error, but will try to harden the solution a bit further.
Make sure that you’ve selected the right case in the dropdown.
Then row 13-20 should appear on the “Instructions”-page.
/Imke
Hi Imke,
Your tool is amazing! I have just cleaned up a pbix file that had a lot of measures, calculated tables and columns etc. The tool worked perfectly except in 2 instances where it recommended to delete but they are required:
a column which was used in a pie chart as part of a hierarchy
a column which was used as part of a condition in the code for a calculated table – see below – it recommended getting rid of CurrentMonthEndDate column:
ADDCOLUMNS(
table here,
name of column,
IF( BambooHR[Hire Date] <= [EndOfMonth] && (ISBLANK(BambooHR[LeavingDate]) || BambooHR[LeavingDate] > [EndOfMonth]) && ‘Calendar'[EndOfMonth] <= BambooHR[CurrentMonthEndDate],
It also required a few iterations to really clear down all the redundant measures and columns.
Overall, absolutely brilliant and very useful indeed!
Angus
Hi Imke
Just tried it on my most complex pbix. Alas it won’t load. Errors out. There is an error in the cell for the column BinaryText in the query ReportFiles on the Report/Layout row 2 from the step:
#”Added Custom3″ = Table.AddColumn(
#”Filtered Rows3″,
“BinaryText”,
each Json.Document([Custom])
),
The error message is ‘an invalid escape sequence in JSON input’ – value \a Position=20113
Happy to work with you to solve!
Kind regards
Angus
I’m getting an error upon loading. I downloded the older version to see where the error was and it appears to be sourcing from the JSON file RLStaging
DataFormat.Error: We found extra characters at the end of JSON input.
Details:Value=!
Position=230327
Is there a workaround to this?
Hey AI,
Sorry to hear about the trouble you’re having with the cleaner tool.
If you’re using the latest version and it still doesn’t work, I would need the template file (pbit-version) of the file you’re analyzing. This kind of file will not contain any data you’ve loaded into the model itself, just the definition of the file. But as hardcoded data source credentials like API keys would be included, please make sure to replace them by dummy values.
So it would be great if you could save your Power BI file as a pbit and send it over to info@thebiccountant.com.
Thanks and BR, Imke
Hi, thanks for sharing! I tried on one of my reports and it works pretty well apart from one thing that I noticed – have two datasources which I merge together to form my data model, and this tools is indicating that the two original tables are not used and I should delete (which I can’t do as I need to perform my merge). So that’s one area where I had to keep in mind, other than that, great tool to check whether all columns are needed.
Hi Imke, been using your latest version of power BI Cleaner on a quite large complex model and it works brilliantly – I’m very happy – it’s really helping to identify issues within the model.
I have added a calculated column to the table AllFields_Used to separate out the report page:
DashboardPage =
// returns the page name from the Details column
VAR SearchValue = SEARCH(“|”, AllFields_Used[Details],, 0) – 2 // get rid of / and preceding space
RETURN
IF(SearchValue < 0, // equivalent of | not found in string
BLANK(),
LEFT(AllFields_Used[Details], SearchValue)
)
Hello Imke. This is a terrific tool and I’ve started to implement it as a standard process to remove unused columns on all reports. This is an important step in my opinion, because it Limit our exposure to avoidable report refresh errors (where unused columns are removed from the data source), Reduce size of datasets, and Improve performance of dataset refresh.
I’ve implemented this for a few reports but now on the current report I am dealing with the data load fails with an error related to “ReportFiles” query. The query unzips the PBIX file and filters to [FileName] = “DiagramLayout”, “Report/Layout”, “Settings”. The error is at step “Added Custom 2” which calculates [BinaryText] column to create record from json. The error is for “Report/Layout” file and the error message is, “DataFormat.Error: We found extra characters at the end of JSON input. Details: Value=P Position=62203”.
I have unzipped the PBIX and located the “Report/Layout” file. I navigated to character 62203 in the file. It is on line 101 and starts around 1800 character on the line. The element looks like this: [Sections > visualContainers > “config”:”\”name\”:\”87bab159c812f0cfd780\” > “{\”textRuns\”:[{\”value\”:\”Gathering Type (Bison Piped, Trucked …]
My troubleshooting attempt could be way wrong. But that’s what I’ve tried to find. I am unsure how to troubleshoot the issue any further.
Can you help figure out why the query fails? Here is a link to the file copied from unzipped PBIX.
https://github.com/sherlockspreadsheets/Sherlock-Public/blob/main/Troulbeshoot/Report-Layout.json
Correction to my post:
The element looks like this: [Sections > visualContainers > “config”:”\”name\”:\”71244242a401369308c4\” > “{\”textRuns\”:[{\”value\”:\”Gathering Type (Bison Piped, Trucked …]
Hi Imke, this problem persist. Do you have any suggestion on how to fix it?
Hi Przemyslaw,
sorry for the trouble.
But I am retiring support for this tool for now, as you can find an alternative here: https://en.brunner.bi/measurekiller.
Please feel free to use this tool as it is, but I am not fixing any bugs or provide further help for it.
Hello Przemyslaw,
You might try with the Power BI Cleaner version embedded within Power BI Sidetools. You will find it in GitHub where you can also post an issue. I will try to help there.
Hi Imke,
I am trying to clean a dashboard file with a lot of tables in the model. I get an error on fnPortModel ‘Cannot convert type NULL to Type TEXT? is there any workaround for this function?
the link to the excel file is not working
thanks a lot, i got an error after the “ignore” option 🙁
”
AllFields_Used
Query ‘DMV_Columns’ (step ‘Replaced Value’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
DistinctFields
We cannot convert the value null to type Text.
AllFields_DAX
Query ‘AllFields_DAX’ (step ‘Removed Duplicates’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
xtra_ColumnRenames
Query ‘xtra_ColumnRenames’ (step ‘Reordered Columns’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
Hello Eli,
Can you try Power BI Cleaner from Power BI Sidetools. I corrected some bugs there.
Didier Terrien