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
In this post I’m sharing a new version of my Power BI Cleaner tool. Besides from some bug fixes it can now automatically identify and analyze reports that are connected to an Analysis Services Model in tabular mode.
Just a warning that the live connections to Analysis Service or shared Power BI dataset might be slow, as Power Query will retrieve the data from all datasets that are available for your from the service. I might include a feature to pre-select datasets in a future release if there is interest. So please add comments if you want to see this feature.
If you are new to the tool: It shows you which DAX expressions are used where in your reports and also indicates which elements can be deleted or hidden. It covers most use cases from visual fields, titles or format properties over filters on all levels, calculation groups, roles, incremental refresh policies…
As before, there are 2 versions: A basic Power BI version and an Excel-version, that adds some very convenient additional features: The option to analyze thin reports (connected to Analysis Services or a shared Power BI dataset) and to generate scripts that delete unused measures and hides unused columns automatically.
Basic Power BI Cleaner 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
If this prompt is not appearing and privacy levels are still enabled, you might get an error message (ending: “…. Please rebuild this data combination”). You then have to ignore them via the menu: File -> Options and settings -> Options -> Privacy (under: CURRENT FILE) -> Ignore the Privacy Levels..:
Ignore the Privacy Levels through the menu in the Power BI Cleaner
Also, depending on your settings in the global options, you might be prompted with a couple of warning screens about native database queries:
Native Query Warnings
This is to inform you that you’re executing some custom code against a database. This database is the local instance of SQL Analysis Services that is the basis for Power BI Desktop and automatically running in the background. These warning screens will pop up for all the 15 DMV queries unfortunately and you can only disable the warnings in the global settings of Power BI. Which is something I usually don’t recommend for security reasons, as you can run scripts against a DB that alters or deletes records. So this could open doors for malicious code or simply screw things up if you don’t know what you’re doing. So one should handle this very carefully.
Also, if you’re running the pbit for the first time, the refresh might not succeed at the first time and you might see these symbols at the top of the screen:
Apply changes again if opening the pbit for the first time.
Simply hit “Apply changes” again and the results will finally show up. I recommend to save the new pbix as your personal sample and reuse that to avoid having to refresh the file twice in the future.
Main report
Power BI Cleaner main report
Overview page
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.
DAX Expressions page
The last page “DAX Expressions” simply holds a table with all DAX expressions in the model as a reference of to quickly check items. You can also use the search box to find strings in DAX expressions.
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 or use a live connection to an Analysis Services Tabular model (on prem or Azure).
I strongly recommend to close all other Excel files when working with this solution, especially when you’re analyzing thin reports (Analysis Services or PBI Live Connection). It will occupy resources and Excel might also become unresponsive for some time.
For these queries to run, you have to disable the privacy settings on file-level:
Disable privacy levels in Excel
Then you have to select the modus:
Single Power BI Desktop File
If you choose “PBI_Desktop_pbix” the following fields will show up:
In cell B8 you have to enter the port number just like in the PBI version. In B37 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 “ReportFile (s)” from cell B37 onwards 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
On the “Instructions” page of the xlsx choose modus “PBI_Live_Connection”. Paste the copied authentication figures into row 30/31:
Also fill in the port number into B25 and the dataset ID into B26. 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 B27 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 B26, 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)
Reports connected to an Analysis Services model
If you select modus “Analysis_Server” the following dialogue will appear:
Enter the path to the SQL server into B15, or “localhost” if it’s running you your local machine. The database name goes into B16 and the credential in B19/B20 follow the same mechanics than described above under “Directly from the service”.
Optionally, you can add links to local files if they should be analyzed as well.
Just have in mind, that these methods can take quite a while, because in order to identify all reports in the Power BI service that use the specified datasets, Power Query has to retrieve metadata from all datasets and all data sources that live in your tenant (and you have access to).
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.
- Fields used in connected Excel reports are not covered by this solution.
- Detection will not work when sensitivity label are applied.
- Fields in custom visuals might not be properly detected. If you find problems there, please report.
- Certain special characters in measures will lead to false negatives (meaning they will be shows and unused/can be deleted). So far, I could only identify the triangle (∆), but I cannot rule out that other characters can also be affected. The following special signs seem to work alright: |, (, ), \, “.
What you can do instead, is to rename a measure with the triangle sign, once used in the visual. This will be picked up correctly.
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.
Potential Problems
Run-time error ‘1004’
This error message might indicate the your token isn’t valid any more and you have to refresh it:
… please rebuild the data combination – error
If you’re getting a warning that data sources cannot be combined, you must disable privacy settings like so:
Disable privacy levels in Excel
Refresh error: The column ‘Report File(s)’ of the table wasn’t found
Refresh Error
This error will appear when the file to be analyzed isn’t found. So please check your entries in the 2nd parameter and make sure that the full path incl. file name and extension is correctly entered.
If you experience further problems with the Power BI Cleaner tool, please post them in the comments or send me an email and I will do my best to get them sorted.
Current version: 2022-Jan-8: V7 (bugfixes)
Download files
PBIT: Power BI Version Cleaner Gen2
Excel version with macros: Cleaner Gen2 Excel-Version with Macros
Enjoy and stay queryious 😉
Hi Imke, I gave a try this new version today but I’m getting back an error. I went back to the previous version with the same pbix file and everything worked well. The error message is:
DistinctFields
OLE DB or ODBC error: [Espression.Error] The column ‘Level’ of the table wasn’t found..
On a different test, I tried to use the Excel version to connect to a local pbix thin file which dataset is in the service, but I received a 1400 error message. In this scenario I was not able to connect with the previous version of Excel tool either. Maybe I’m missing something here, please advise
Cheers,
Hi Mau,
sorry for the inconvenience, but I cannot reproduce unfortunately.
But I can image where it could come from and tweaked the code a bit.
Please try out the latest version and let me know if it works.
Thanks a lot,
Imke
Pingback: Power BI Cleaner Gen2 – Curated SQL
Hi Imke, please don’t worry, both of your tools are fantastic, love them. I know it must not be easy to cover all the bases. You’ve already done a great service to the community with these tools. Please keep up with this great work. Regarding the latest version, I tested it with two different pbix files, both in import mode. With one of them the Excel tool and the PBI tool worked well. With the other, I received thes error messages:
StorageSize
Column’Key’ in table ‘StorageSize’ contains a duplicate value ‘ClubView.Club Name’ and this is not allowed for columns on the one side of a many-to-many relationship
AllFields_DAX
Query ‘AllFields_DAX’ (step ‘Removed Duplicates’) references other queries or steps, so it may not directly access a data source
I don’t know if it is something in my file or something that could be fixed in the tools, but this info helps.
Cheers!
Hi Mau,
sorry for the inconvenience.
Will fix the duplicate error with the next release.
With regards to the other error, you have to disable privacy settings (this is described in the blogpost).
Cheers,
Imke
Hi Mau,
I’ve uploaded a new version that fixed the error with the duplicate key.
Hope it now works for you, Imke
I’ve tried the the powerbi & excel version , it works very fine and fast with a local pbi connection .
I did not arrive to connect to a dataset in the service , but this is rather ‘a nice to have’ for me .
Thanks for all this great work and sharing this with us .
I will certainly use this tool
Kind regards
Alain
Hi Alain,
Thank you for the kind feedback.
Over time I will add more documentation on how to use this tool which will hopefully help you to use the “nice to have”-features as well 🙂
/Imke
Hi Imke,
I’ve followed all steps correctly but keep getting this after trying to apply the changes post the local host step. What could be wrong here?
AllFields_DAX
Loading blocked by failures with other queries.
AllFields_Used
The specified distinct criteria is invalid.
DistinctFields
The specified distinct criteria is invalid.
StorageSize
Loading blocked by failures with other queries.
/Mido
Any solution to this?
I got the same problem.
Thanks,
Simao
Hi Mido,
sorry for the inconvenience.
Are you using the Excel-version or Power BI?
And if it is the Excel-Version: Which modus?
/Imke
Hi Imke,
Thanks for the response.
I am using the Power BI Version
/Mido
I’m getting the same issue here and I’m using Power BI version.
I’m getting the same issue on all Power BI Files.
Files are local OneDrive if that if relevant.
I am getting this refresh error as well. I even changed all the data sources so the queries point to my local SQL Server – everything is on the local machine. Followed instructions on disabling privacy levels in Options.
When I go into the ‘AllFields_Used’ query in PQ, it fails on the 2nd step and says, “An error occurred in the ‘UsedFields1_Visuals’ query. Expression.Error: The specified distinct criteria is invalid. Details: [List]”. So when I go to ‘UsedFields1_Visuals’ query it fails on the 2nd step and says, “DataFormat.Error: We found extra characters at the end of JSON input. Details: Value=! Position=634528”. I have no idea how to fix that, but maybe that’ll help someone!
Thanks a lot for this additional info.
Will try to check this over the weekend.
Hi Imke
I face same problem , thanks in advance for your advice
Ivan
Hi everyone,
unfortunately I’m not able to reproduce this error.
I anyone would be able to send me their file (pbix or pbit-version) to investigate, that would be very helpful.
Thanks and best regards, Imke
Edit 2021-Oct-24: I could identify and fix one possible reason: When special signs were used in DAX expression names or renames of fields in visuals.
Please download the newest version (V5) and see if that fixes your problems.
Also having this issue. Hope he was able to get to a fix this weekend.
Thank you Imke. I’m seeing similar errors to those noted in this thread. I have reviewed my field lists and some fields include punctuation characters such as ? or %. Do these fall into your definition of “special characters”? I have captured all error messages and warning I’ve received in a Word Doc and can forward that to you if you like.
My file contains the following suspect characters in field and/or measure names % / . ( ) ?
I am using the latest version of your tool (PowerBI-Cleaner-Gen2-V5-1.pbit) just downloaded today 🙂
Hi Larry,
yes, these are the characters I meant. But I tested them and they don’t cause the actual problem.
As it looks, my latest adjustments destroyed the proper recognition of the text visuals (they now contain lots of new logic and fields related to the smart narratives).
Very sorry about that, will try to solve this over the weekend.
/Imke
Hello, I too am having the StorageSize issue when using the Power BI file. For me it says:
“Column ‘Key’ in Table ‘StorageSize’ contains a duplicate value ‘Storage Location.STORAGE LOCATION CODE’ and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.”
Thanks
Hi Chase,
please download the latest version to check if my fix has solved the problem.
Thanks, Imke
Hi, I have tried this with version 5 and am still seeing the issue with duplicate field. In my case one field is spelt “Fee” the other “fee”
Hi Imke:
I am trying out the tool and am having a field tagged as ‘Delete’ when it is used directly in a visual, any steps/FAQ you can point me to?
I have a screen shot of the output if that would help.
Hi Dan,
thanks for reporting.
Which visual is that?
/Imke
Its showing on the overview screen. One of the fields is used as an implicit measure so on the visual it appears as ‘First Audit Date’ instead of ‘Audit Date’. That one is used on multiple card visuals. I just realized the other one is used on a custom visual – paypal KPI Donut Chart.
Hi Dan,
unfortunately I cannot reproduce the issue with the implicit measures on card visuals (or table visuals).
Have tried with both numeric and string fields – they all show as used.
Please make sure to use the latest versions.
If that doesn’t help, would it be possible to share sample file for me to investigate?
Thanks and cheers,
Imke
Hi lmke
unfortunately when I try to connect directly to a tabular model in SSAS I got these errors:
We cannot convert the value null to type Text. And I’m using the newest version.
Thanks in advance
Best regards
Brian
Hi Brian,
that’s an indication that the connection couldn’t been made successfully. Please check your input parameters.
Hi Imke,
Thank you for this life-changing Power BI developer tool!!!!
Have an amazing day
Pingback: Clean your POWER BI Model - Really Fast! - Goodly
Hi Imke,
Your tool looks fantastic and would love to use it, however I am facing some issues. When I try to connect to my data model it gets stucks at “Evaluating” while loading the queries…
Do you maybe have a suggestion what could cause this? Thanks in advance
Edit: I find out that the problem is caused by a calculated table in my model. After deleting the calculated table it does work
Very promising. Unfortunately it is not detecting usage in visuals at all. Can you think of any reason that would be or any troubleshooting steps I could take?
Hi Tom,
sorry about the trouble.
Which version are you using? Power BI or Excel? If Excel, which mode?
You can check the query “UsedFields1_Visuals” to see where the error occurs.
If the first already throws the error, then there might be an issue with the file itself or some custom visuals.
Happy to investigate further if you send me a file or do a Teams call.
/Imke
Hi Imke,
Using the PowerBI version. No error in that query, it is just empty from the source down. The only error is in SectionProperies_RLFilters. That throws the error “The column ‘ColumnName’ of the table wasn’t found.
Details:
ColumnName”
in the Merged Columns step.
I get the same behavior with every pbix file I’ve tried, so I’m not sure sending a file would do anything. How would we go about doing a Teams call?
Tom
Hi lmke
I am trying to use the PBI tool but I am stuck in the ‘Refresh’ windows, all tables trying to ‘Creating connection in model’ for ever 🙁
What did I do wrong ?
Thanks in advance
Alain
Hi
I also tried the Excel tool (both with PBI Desktop and PBI Live Connection methods) and got a run time error when refreshing the AllFields_Used query.
The error is : running out of memory 🙁
Please help – Thank you
Alain
Hi Imke.
Been running for 7+ hours on my model now and Allfields_DAX is at close to 61mio rows and counting..!
Very exited to finally see the result at some point 🙂
/Rasmus
It timed out after 12+ hours 🙁
Sorry to hear, Rasmus.
Which version/modus are you using?
It looks like it does not take into consideration fields that are used as filters in filter pane, but not in any of the visuals. Such fields are marked for deletion.
Sorry about that and thanks for reporting.
I was missing an error-handler there.
Just uploaded a new version, please check it out.
Thx, Imke
I am also having this issue. I can’t download the data model .pbix because we are using XMLA endpoint. I input the PPU workspace connection in the portnumber field on the excel file and it does not load anything. I don’t receive an error, but there is nothing in the Result tab.
I get this error in the PBIT version. any help?
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.
Dear Imke,
I’m getting the following error in Power BI when connecting to my report, can you help please?
AllFields_Used
OLE DB or ODBC error: [Expression.Error] We cannot convert the value “Measure” to type Record..
DistinctFields
OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.
Thanks and keep up the great work you are doing!
Nik
Hi Imke,
The above was my mistake, I had to refresh PBI to the most recent version. However now I’m getting the following errors:
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.
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
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.
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.
Thanks in advance!
Hi Nikolas,
you can solve this by Ignoring Privacy Levels like displayed at the end of the blogpost.
Cheers, Imke
Hi Imke,
Thank you but I did that as well.. It seems it’s pbix specific because when I tried to open a smaller report with your cleaner, it went through without issues. However the initial report which I wanted to analyze is still failing:
On the first run I get the following error:
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.
I hit close, then I select Apply changes, the data starts loading, but it fails around 55k rows and 524MB with the following error:
AllFields_Used
OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.
DistinctFields
OLE DB or ODBC error: [Expression.Error] We cannot convert the value “Measure” to type Record.
Is there a report size limitation perhaps?
Thanks, Nikolas
Hi I’m using the PBI version
and I’m getting errors once I apply change
AllFields_DAX
Loading blocked by failures with other queries.
AllFields_Used
The column ‘Report File(s)’ of the table wasn’t found.
DistinctFields
The column ‘Report File(s)’ of the table wasn’t found.
xtra_ColumnRenames
Loading blocked by failures with other queries.
StorageSize
Loading blocked by failures with other queries.
Thanks 🙂
Hi reut, a
are you able to share your file (pbix or pbit – template without data)?
Thanks, Imke
Hi Imke,
which file you want me to share? the PBI-Cleaner-Gen2-V7?
Thanks
Hi reut,
sorry about the late response, was very busy lately.
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 there, i am having this same issue. Did you manage to see why this happened?
Hi Imke,
Thank you for putting this together, it has been really useful! Am currently running the Excel version with a modus of PBI_Desktop. Privacy levels are off though there is this error:
Run-time error ‘1004’:
We couldn’t refresh the connection ‘Query- StorageSize’. Here’s the error message we got:
Query ‘fnPortModel’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
If you are able to assist here it would be really useful. The same connection when made in the Power BI version of report cleaner worked, the only difference seems to be that in Power BI I had to specify the report file and port number, for the Excel version only a port number was provided (though I did also try adding the file path to the ‘pbix files that are connected to the dataset/model’ column). Once Excel report cleaner can run on this single pbix am looking to include the thin pbixs that use said model as their connected dataset.
Hi Redmond,
you have to disable privacy levels like described at the end of the article.
Cheers, Imke
If anyone knows how to find the port for a Service dataset that Excel will access directly it would be really appreciated.
Good morning Imke. First fantastic tool!! Its awesome. In the last week or two I make some model changes however that cause the refresh to fail. I’ve never had that on another model with the tool. If there’s a way to send you logs or a screenshot let me know.
I thought maybe there would be a new version released that might fix the issue, and there is a newer version than I have but the download link isn’t working.
https://www.thebiccountant.com/download/9460/
Hi Trevor,
sorry to hear about your trouble. You are using an old download link. Please use the links from this page: https://www.thebiccountant.com/2021/08/23/power-bi-cleaner-gen2-now-covers-analysis-services-models-as-well/
The download IDs there are 9295 and 9255.
Otherwise please send an email to info at thebiccountant.com
Thx, Imke
Hi Imke,
I managed to connect to the local instance of Power Bi Desktop using the PortNumber shown inside DAX Studio. It works!
So I tried to connect to a complex report I have that has this time the datamodel & DAX measures on the premium capacity.
I do not have anymore a PortNumber in DAX Studio, I have the premium capacity link (workspace connexion).
When I put this information inside the PortNumber I receive the following error message:
“AnalysisServices: A connection cannot be made. Ensure that the server is running”.
I am doing something wrong? It would really helpful to be able to identify which of my hundreds of DAX measures are not used and make sure I only keep what it is needed.
Thank you in advance for your help,
Best regards,
Alex
Hi Alex,
that should work.
Let me check this out later and come back.
BR, Imke
Hi Imke,
Thanks for great tool! It’s been of great help while working with models 🙂
I have one idea/tip that I’ve found very useful. Could you add condititonal formatting for column “Can be deleted/hidden” in a matrix on Overview page in PBIT version?
I’m using a measure to conditionally format background of the column – it’s just easier to work with 🙂
sometthing like this should be fine:
Can be deleted/hidden Colour =
SWITCH([Can be deleted/hidden],
“delete”,”#FF7F7F “,
“keep”,”#AED9B2″,
“delete & keep”,”#FFFFE0″,
“#FFFFFF00”)
All best!
Łukasz
Hello Lukasz,
thanks for this suggestion – will include this in the next version 🙂
Cheers, Imke
Hello Imke!
I miss not seeing you this year, but glad I saw you on Reid’s Livestream.
I used you excel version with a dataset model loaded in PBI Desktop, and ran you excel macro file referencing 6 this reports with the sole purpose of finding out what columns are not used so that I could remove them. It has been 6 hours, but time for bed, so I’ll see where it is at tomorrow morning. I wonder if it makes sense simply to run the thin reports 1 at a time. The thin files at PBIX – would it be better to convert them to PBIT? Or is it just the cross checking that it is doing for the unused columns?
Looks like an amazing tool and looking forward to seeing what it produces. Thanks so much for this fantastic community contribution!
I ended up cancelling the job, then turning off the security checks and running just the one PBIX model fat file in the Excel tool, not listing the thin files in cell B37.
I am timing it to get a sense of how long it takes, then I will do another test with doing the 1 fat and the smallest of the thin files.
Hi Imke,
You tool is so great!
Unfortunately it is not working on one of our main PBIX-files. It seems to be running forever (always showing “Evaluating” for AllFields_DAX, AllFields_Used and Distinct_Fields.
I am using the most current version and privacy settings are disabled. Do you have any idea what could be the reason?
Thanks and best regards,
Julian
Hi Julian,
yes, I’ve come across one instance lately where my tool ran into an endless loop. However, as I didn’t have access to the file itself, I wasn’t able to troubleshoot it yet.
You you by any chance be able to share the pbit-version of the file you’re analyzing?
That would allow to try to find the cause.
Thanks a lot,
Imke
Hi Imke,
I am really excited to try your tool, but unfortunately I cannot connect to it yet. I have a main question: Does this work for live models?
My goal is to extract the measure from a book connected to a live model. To find the localhost of the model, I had to apply changes to the model in power bi desktop to be able to access daxstudio and from there, I extracted the localhost, as you indicated in your instructions. However, as shown below, I still cannot connect to it.
(The privacy boxes should stay the same as yours)
Error:
AllFields_DAX
AnalysisServices: A connection cannot be made. Ensure that the server is running.
AllFields_Used
AnalysisServices: A connection cannot be made. Ensure that the server is running.
DistinctFields
AnalysisServices: A connection cannot be made. Ensure that the server is running.
xtra_ColumnRenames
AnalysisServices: A connection cannot be made. Ensure that the server is running.
StorageSize
Loading blocked by failures with other queries.
Hi! Great work! I used to be able to make your product work, but unfortunatly I got an error with this new version.
Seems to be with the fnUnzipFile function…
Here’s the error :
“An error occurred in the ‘fnUnzipFile’ query. Expression.Error: We cannot convert the value “C:...” to type Binary.
Details:
Value=C:\Users\Desktop\xxx.pbix
Type=[Type]”
Thanks for your help!
Hi Imke, a great innovative tool you’ve built.
Unforutnatley, I ran into a problem. After the step where you ignore privacy levels, the data model refreshes and then an error appears with the following message:
AllFields_DAX
AnalysisServices: A connection cannot be made. Ensure that the server is running.
Wondering if you could help with this?
Thank you.
Hello,
This is great !! Thanks for the indepth explanation. Have a few questions if this can be answered.
I am using shared dataset (Data Model) and source is Microsoft Dataverse. Is it still possible to get which columns are being used and which are not ?
Data Model resides in a different workspace and the 2 reports are in the other
Am trying to optimize the Model since i am getting the error about taking too long time to process the model.
Thanks in advance
Ronak
Hi Imke,
thank you for this great tool!
I stumbled upon a bug in the powerquery code for the table UserFields1_CondFormatVisual which prevents the report from loading.
At step “Lowercased Text4” an error is generated in the column “Value”.
In the previous Step the majority of rows contain a record value in the value column. But there are also five rows containing the numeric value 3 in the value column which can’t be transformed into tables.
The report can be loaded if i remove the rows with the error.
Kind Regards
Hi Imke,
i found out that reports which use the conditional formatting for web urls in the visual styling section can’t be processed by the pbi cleaner. This leads to the error described in my previous post. With web url formatting switched off the report can be loaded without any problems.
Kind Regards
Imke,
I am sure this is a great product, Brian Julius commends this.
I am getting a couple of errors that I am not sure how to get around them or correct them.
Below is part of the errors during the Refresh:
Refresh
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.
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
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.
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.
StorageSize
Loading blocked by failures with other queries.
Thank you
Jim Rubino
Hi ! Am using this for the first time – 1/8/22 V7 (bug fixes) And I am getting the following the error:
AllFields_DAX
Loading blocked by failures with other queries.
AllFields_Used
The column ‘Report File(s)’ of the table wasn’t found.
DistinctFields
The column ‘Report File(s)’ of the table wasn’t found.
xtra_ColumnRenames
Loading blocked by failures with other queries.
StorageSize
Loading blocked by failures with other queries.
Any suggestions?
Hi. Thank you for this wonderful work. I wish I could make use of this work. But I am facing the same issue as above. Is this sorted yet? Any recommendations?
Try to enter file name put extension at its end… for eg. “C:/Users/XOXO/Desktop/unititled.pbix”
This will most likely solve your issue
how do I refresh the token?
when I go to your link “Datasets – Get Datasets (Power BI Power BI REST APIs) | Microsoft Docs” I don’t the the “Try it” option. is this only for me?
Hi Noah,
I’m sorry, but the token generation currently doesn’t work. (See first sentence of the blogpost)
Microsoft is currently working on getting it back (soon, hopefully).
/Imke
Hello, I get errors when I enter port and filepath:
AllFields_Used
The column ‘Report File(s)’ of the table wasn’t found
DistinctFields
The column ‘Report File(s)’ of the table wasn’t found
I believe I discovered my error! Did not specify full file path in second parameter, including file name.pbix!
Hi Imke,
Learned about the tool from Reid Havens and downloaded it right away. Looks simple but cool tool to find unused columns. I was able to follow instructions until ‘Simply hit “Apply changes” again and the results will finally show up’ step. Instead of results I get an error which Martina got back in February 2022.
AllFields_DAX
AnalysisServices: A connection cannot be made. Ensure that the server is running.
AllFields_Used
AnalysisServices: A connection cannot be made. Ensure that the server is running.
Do you know what is it complaining about and how do I resolve it?
Hi,
It is coming with an error while trying to upload the data in all Tables:
“We cannot convert the value null to type Text”……
This is happening on PBI-Cleaner-Gen2-V7 file I just downloaded above.
Hi,
I’m facing a similar issue. Was any resolution found for it?
Pingback: Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity – Olivier Travers
This tool is amazing! Thank you! Quick Question: if two columns are combined in Power Query, and the resulting column is used in the report visuals but the two source columns are not, will the tool show those two as “used” or “not used”? Same thing with a conditional column that references other columns to determine if conditions are true/false. I have a lot of situations like that.
Hello,
I am trying this tool but i get the following error:
An error occurred in the ‘fnUnzipFile’ query. DataSource.Error: Could not find file ‘C:\UsersXXX\XXX
Please help me further.
Dear makers,
I was tipped by Access Analytics on Youtube to use your product. I however experience simular issues as other responders. I hope you can tip me to resolve.
thanks in advance for your help!
AllFields_DAX
AnalysisServices: A connection cannot be made. Ensure that the server is running.
AllFields_Used
AnalysisServices: A connection cannot be made. Ensure that the server is running.
DistinctFields
AnalysisServices: A connection cannot be made. Ensure that the server is running.
xtra_ColumnRenames
AnalysisServices: A connection cannot be made. Ensure that the server is running.
StorageSize
Loading blocked by failures with other queries.
Hi
I’m using Gen2 V7 that I downloaded today.
I get the following error when trying to load the data:
AllFields_Used
OLE DB or ODBC error: [Expression.Error] We cannot convert the value 3 to type Record.
Any help you can offer will be gratefully received.
Thanks
Hello. I’m getting the “The column ‘Report File(s)’ of the table wasn’t found.” error. I’ve confirmed port number and full file path are correct. Any help is greatly appreciated.
I am also getting this issue. wonder if its related to the latest version of power bi?
Hello,
You’ll get this error if the json conversion failed which can happen if you used some special character in your report such as “≤” which unfortunately prevent the json conversion from your report data.
very good
Hi I know my powerBI is extremely large but it seems to just get stuck at the refresh page.
Can it be too large or could the fact it is using dataflows be an issue.
Refresh
AllFields_DAX
Evaluating…
AllFields_Used
Evaluating…
DistinctFields
Evaluating…
xtra_ColumnRenames
Waiting for other queries…
StorageSize
Waiting for other queries…
Hi,
I downloaded the last pbit-file.
When I try to open it, I get a message that the file is corrupted and cannot be opened.
I used the Power BI Desktop version of september 2022.
Can anyone help me?
Thanks
Great tool, thanks 🙂
ISSUE
I’m connected to a SSAS connected PBIX, but cant see the report level measures?
INSTRUCTION pane
I’ve entered Server, Database and Report files path
I havnt entered anything in Token details
RESULT pane:
Tabels, columns, cube measures, DAX are showing fine.
But not report level measures?
How come?
Hi Henrik,
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.
Hi Imke. Thanks for letting me know! Regards, Henri
Hi,
Please, I downloaded the files, but I do not know how to add them to Power BI and Excel (I mean to show the icon or ribbon tab inside the Excel and Power BI.
Please advise.
Thanks,
Mohamed
Hi Mohamed,
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.
Thanks a lot my friend.
I do understand, no problem.
Good luck 🙂
Im getting following error when i try to do the clean up
“OLE DB or ODBC error: [Expression.Error] We cannot convert the value 3 to type Record.”
How Does Snapchat Work
How Does Snapchat Work
Your web log isn’t only useful but it is additionally really creative too.
I want to read more of your posts in the future.
wish to read more of your future postings.