Power BI Cleaner Gen2 now covers Analysis Services Models as well

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

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)



  • 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.


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


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 😉

Comments (60) Write a comment

  1. 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:

    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



    • 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,


  2. Pingback: Power BI Cleaner Gen2 – Curated SQL

  3. 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:

    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

    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.



    • 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).


      • Hi Mau,
        I’ve uploaded a new version that fixed the error with the duplicate key.
        Hope it now works for you, Imke


  4. 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



    • 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 🙂


  5. 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?

    Loading blocked by failures with other queries.

    The specified distinct criteria is invalid.

    The specified distinct criteria is invalid.

    Loading blocked by failures with other queries.



    • Hi Mido,
      sorry for the inconvenience.
      Are you using the Excel-version or Power BI?
      And if it is the Excel-Version: Which modus?


    • 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 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.

      • 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.

  6. 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.”



    • 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”


  7. 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.


  8. 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,


  9. 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


    • Hi Brian,
      that’s an indication that the connection couldn’t been made successfully. Please check your input parameters.


  10. Pingback: Clean your POWER BI Model - Really Fast! - Goodly

  11. 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


  12. 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.


      • 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.

        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?



  13. 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


    • 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


  14. 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 🙂



  15. 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


  16. I get this error in the PBIT version. any help?
    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.


  17. Dear Imke,
    I’m getting the following error in Power BI when connecting to my report, can you help please?
    OLE DB or ODBC error: [Expression.Error] We cannot convert the value “Measure” to type Record..
    OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.
    Thanks and keep up the great work you are doing!


    • 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:
      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.
      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.
      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.
      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:
          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:
          OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.
          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


  18. Hi I’m using the PBI version
    and I’m getting errors once I apply change

    Loading blocked by failures with other queries.
    The column ‘Report File(s)’ of the table wasn’t found.
    The column ‘Report File(s)’ of the table wasn’t found.
    Loading blocked by failures with other queries.
    Loading blocked by failures with other queries.

    Thanks 🙂


  19. 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


  20. If anyone knows how to find the port for a Service dataset that Excel will access directly it would be really appreciated.


  21. 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.



  22. 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,


Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz