Tidy up Power BI models with the Power BI Cleaner tool

Edit 27th June 2021: There is a complete new version of this tool that has all the bugfixes and enhancements here: https://wp.me/p6lgsG-2pi

Edit 23th June 2020: Updated version (see download link at the end of the article) to reflect changes with the vpax fileformat in DAX Studio versions V11.0 upwards.

The VertiPaq-Analyzer tool is one of the great community tools that I really cannot live without. It gives me a great overview of all elements in my model and identifies potential performance problems by showing the storage requirements of each column. So when seeing expensive columns, the first question that arises is: “Do I really need this column or could I delete it?”. Luckily, this can now be answered with my new Power BI Cleaner tool. This tool shows the usage of all columns (and measures) within the tables of the VertiPaq Analyzer.

Power BI Cleaner shows unused columns in the VertiPaq-tables

Power BI Cleaner tool

So whenever there is no entry in the column “Where Used” you can go ahead and eliminate the column (or measure) from the model. Well – with two exception actually: Fields used in the definition of incremental load policies are currently not identified as well as used fields in calculation groups. So make sure to consider this before running wild 😉

In the table “Measures” you can spot which measures might not be used:

Identify unused measures with Power BI Cleaner Tool

And as a general recommendation: Always make sure to have your old version still in place and create a new version for your cleaned up file, just in case.

To learn more about the used elements, you can drill through to detail pages “Where Used Direct” and “Where used Indirect”:

Drill through to detail pages

“Where Used Direct” will show where the elements are directly used in:

Analyse directly used elements

And the next page “Where Used Indirect” will show elements that are using the selection indirectly at a later stage as well:

Elements that are indirectly used at a later stage

Please note that the drill through pages will only return values for columns and measures that have been used or referenced by used items (directly or indirectly) . So if a column or measure has been used in another column or measure this will not be shown as long as those elements haven’t been used anywhere in the report. This is intentional and reflects the purpose of the tool to be used as a cleanup-helper and not as a documentation tool.

What’s (not) covered

The areas covered for usage detection are the following:

  • Calculated columns
  • Measures
  • Relationships
  • Filters (on visual, page, report and drillthrough-level)
  • Visuals
  • Groups
  • Roles
  • Conditional formatting

And again – NOT covered are:

  • Incremental load policies

Please let me know if there are areas that I’ve missed and I’ll see if I can include them as well.

Further limitations

  • Columns that are only referenced by their name (without the preceding table name) will NOT be detected.
  • False positives will be generated for measures that have the same name than column names: Unused measures will be flagged as  used, if their column counterparts are used!
  • Some custom visuals produce bad metadata which can lead to errors as well. The Network Navigator for example, renames the input table to “Table1” in the metadata. These fields will not be discoverable through this tool.
  • Column and measure names including brackets “()” will currently not be picked up (this limitation might be lifted in future updates)
  • There is a problem with the automatic escaping of Text boxes. So special characters in there might cause problems.
  • It doesn’t work on models that are using live connections

How to use

  1. Retrieve vpax-file with DAX-Studio and paste path to vpax-file into parameter “FilePathVPAX”. ( ! This is a preview-feature that you have to enable like described here: https://www.sqlbi.com/blog/marco/2019/09/15/vertipaq-analyzer-2-0-preview-1/ )
  2. Transform pbix to pbit and paste path for pbit into parameter “FilePathPBIT” (This facilitates showing of “WhereUsed”-column in page “Tables”)
  3. Ignore Privacy Levels (File -> Options and Settings -> Options -> Current File -> Privacy -> Privacy Levels -> check “Ignore the Privacy Levels….”)
  4. Refresh All

Or check out this video for a detailed walkthrough:

How does it work

The tool uses the library and functions of the VertiPaq Analyzer and many metadata parsing algorithms of my Power BI Comparer tool. After all I want to thank Marco Russo for the support and confirmation that it is totally OK to adopt their open sourced solutions to other tools.

All the logic of this solution sits in the Power Queries. So do you want to know how this all works? Then just open the query editor and study the queries.

Download file:  PowerBICleanerV11_upload.zip (Rename the xxx.zip to xxx.pbix and you can use it right away.)
The latest version is V11. Please check out this blogpost for more details.

Troubleshooting: If the file doesn’t work for you despite checking all prerequisites and limitations, please send me your pbit and vpax files so I can examine further. (info at thebiccountant.com)

Enjoy and stay queryious 😉

Comments (100) Write a comment

  1. Tried to use the PBI Cleaner file but am getting errors when trying to refresh, multiple queries are giving errors with a [Source] column, tried to step through the queries to fix but struggling to tell what should be setup to fix in some cases. Can you please advise? Tx

    Reply

  2. Thanks so much for sharing this project – it looks extremely useful.

    I tried it out on a file just now (that I need to tidy up), and first struck the same error as Chris (ref earlier comment). While the actual error was avoided using the v2 version, the report then doesnt show any results for Where Used = Visuals, Relationships, DAX Expressions, possibly others. All I see in Where Used is columns and hierarchies.

    As I described in an earlier comment, my query: UsedFields1 is showing an error at step: _GetRoles –

    Expression.Error: The field ‘roles’ of the record wasn’t found.

    I also noticed my query: UsedFields2 is showing an error at step: Result –

    Expression.Error: We cannot apply field access to the type Null.

    I tried deleting one unused Custom Visual – the report still uses Bullet Chart by OKViz which is up-to-date.

    I hope that info is useful. I can share the pbit and vpax files if that helps. I’m very keen to help get this working.

    Thanks again, Mike

    Reply

    • Thanks Mike,
      currently working on another update – might upload later today!
      Cheers, Imke

      Reply

  3. This is a great addition to vertipaq analyzer. It’ll remove the manual effort of finding references, especially within visuals.
    Will this be integrated into DAX Studio?
    Will there be any effort to detect columns without a table name?

    Reply

    • Hi Jon,
      thanks for the feedback.
      There will be no integration with DAX-Studio, as the data for the visuals isn’t included in any DMV.
      I could possibly create a version where detecting column names without the table name is an option. But as you can use the same column name in multiple tables, this would create false positives for those cases.
      So this would require additional documentation to highlight that risk. Let me see when I can manage that.
      /Imke

      Reply

  4. Imke,

    I have 3 measures

    [Total Sales]
    [PY Sales]
    [% YOY Sales] =DIVIDE([TotalSales]-[PY Sales],[PY Sales])

    On the measures tab for [PY Sales] “WhereUsed” is empty.

    I don’t think this is intentional, or is it?

    Reply

    • Hi Frank,
      hast [% YOY Sales] been used anywhere in the report?

      [PY Sales] should only be shown in the “Where Used” if it has been used somewhere in the report. Directly or indirectly.
      So if [% YOY Sales] hasn’t been used anywhere in the report the measures that define it also shouldn’t show up.

      Otherwise: I have a new version with some bugfixes that you might want to try. Just give a me a shout please.
      /Imke

      Reply

      • Thanks!
        I expected “DAX expression” to be shown, but your approach makes more sense.

        Reply

  5. Hi Imke,

    This tool is great but I have the same problem as Frank Tonsen.
    I can assure that [% YOY Sales] is used in my PowerBi but I get still no use for [Total Sales] & [PY Sales]. I would like to test this updated version you mentioned.

    Reply

      • Hi Imke
        I’ve tested the new version and the Problem still persists, but I can tell you that it is another Problem than I thought. The real problem is, that some measures and columns are not detected as used. Therefore the indirect used measures are also not detected.
        At the moment I cannot tell you anything these measures and columns have in common. The problem seems to appear completely random.

        Nevertheless is your tool very helpful, but I have to be very carefully before deleting anything.

        Reply

        • Hi Ilona,
          that’s a pity.
          If you could share your pbit and vpax-file, I’m happy to investigate.
          info at thebiccountant.com
          Thanks and cheers, Imke

          Reply

          • Hi Imke,

            Its’ really kind, that you offer to investigate, but I am not allowed to share these files with you.

            Kind regards
            Ilona

  6. Pingback: What are the options when a Power BI report is slow? - Power BI Performance Guide

  7. Hi, I love the Power BI Cleaner tool! I’ve spotted a potential bug, so I thought I’d post about it to help ongoing development of the tool. I’m using measures to create dynamic card titles, and where I’ve used conditional formatting of a card’s title to directly reference a measure, that measure doesn’t get reported as being used.

    Reply

    • Hi KyokoHunter,
      thanks for this hint – will try to fix it with the next release.
      /Imke

      Reply

    • Have uploaded a new version that picks up DAX expressions used in conditional formatting rules as well.

      Reply

  8. Hi Imke,

    Thank you so much for this tool – very useful.

    Could you please add a usage detection for conditional formatting on titles?

    Thanks!

    Reply

  9. Hi Imke,
    Great tool! Keep on going this project.
    Is there a one link we can find the latest version now and in the future?

    Reply

  10. Hi Imke

    Many thanks for this wonderful tool.
    Does this have an issue working with pbix files which has ‘Store datasets using enhanced metadata format’ enabled? Bcoz on such files, I am getting an error in the ‘Columns’ table – “We found extra characters at the end of JSON input.” when the data is refreshed.

    Reply

  11. Does this tool works with Live Connection PBIX ? I’m trying to use with PBIX which connects to Azure Analysis Service when I try I get error Error loading queries from other visuals

    Reply

    • Hi Nathan,
      it cannot work with live connection, as no query or model data is stored in pbix/pbit.
      /Imke

      Reply

  12. Hi – I’m getting an error after setting the paramaters for my file names and refreshing. for the FieldUsageDetails, i get this error – FieldUsageDetails
    OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. Any ideas whats causing this?

    Reply

    • No, haven’t come across this error message.
      But you can find a lot on the internet about it.
      /Imke

      Reply

  13. Not sure why that happened but my file ended up showing a duplicate for each item in the measures list, 1 with the proper data type, 1 showing null. i did a remove duplicates step on the query, and now it ran. Thanks for creating this.

    Reply

  14. Hi Imke! Thanks for providing this tool. It looks very promising. However I seem to have several measures that are reported as not used (column “Used In” in the Measures report page is empty for the measure) although I indeed see that they are used in a visual. I cannot see anything particular with these measures.How can I troubleshoot this? i downloaded the latest version of the tool today fyi.

    Thanks
    Fredrik

    Reply

    • Hi Fredrik,
      sorry to hear.
      I believe you’ve read the section about limitations?
      There is no easy way to troubleshoot this other than stepping through the underlying queries.

      Would you be able to share your files (vpax and pbit)? I’d be very interested to sort this out.
      Thanks, Imke

      Reply

      • Sorry for the late reply, I did not receive any notification (or maybe it’s in the spam folder). I will certainly dig up the files and share with you. I have read the limitations and don’t think they apply.

        Thanks Fredrik

        Reply

  15. Hi Imke, I am working with version (V7) and spotted some issues with “Where used” column.
    It shows blanks where in fact a column was used in a table visual or another one as a visual filter. In a first case the “Merge Queries” step in “Filed Usage” query retrieves null values when the columns used in join does not precisely match each other.

    Reply

  16. Calculation Items
    Loading blocked by failures with other queries.
    Columns
    Loading blocked by failures with other queries.
    Columns Segments
    Loading blocked by failures with other queries.
    Columns Hierarchies
    Loading blocked by failures with other queries.
    Measures List
    Loading blocked by failures with other queries.
    Relationships
    Loading blocked by failures with other queries.
    Roles Expressions
    Loading blocked by failures with other queries.
    Tables
    Loading blocked by failures with other queries.
    User Hierarchies
    Loading blocked by failures with other queries.
    FieldUsageDetails
    WhereUsed

    Reply

    • Hi Viktor,
      sorry to see that you’ve run into issues.
      Are you able to share the files you’ve used? info at thebiccountant.com
      Thanks,
      /Imke

      Reply

  17. Wow, Imke, what a great work you did! Helped me a lot with my around-100 tables model. Please advise if I can donate some money for using it, you saved me hours of work.

    Reply

    • Thanks Maria for the feedback. Very glad to hear it helped.
      This tool is a free contribution to the community.
      It would be great if you could pay it forward or make a donation to a charity of your choice.
      Thanks and cheers,
      Imke

      Reply

  18. Hey Imke, what a great work . I’m starting to useit and have already removed the unnecessary fields for some powerBI applications . The content is much completer then powerBI helper from Radacad. The only issue I’m facing is that the update of the report is very slow ( 30 – 45 min ) and in query editor it is very very slow to make changes if necessary. Did you ever had the same issue ? I’m running it on laptop with 16 gb of memory .

    Reply

    • Hi Alain,
      make sure that background refresh is disabled (see first point here: https://www.thebiccountant.com/speedperformance-aspects/ )
      Otherwise: These queries take quite some time to refresh, because there is a lot to be done. (As you have recognized, it covers areas no other tool is doing currently).
      But I haven’t had a workbook where the refresh would take that long.
      How many measures do you have in there? And how many visuals? (Roughly)
      /Imke

      Reply

  19. Hi Imke,

    Thank you for sharing the tool and it is super useful for data cleansing!

    I have a question on how to use your tool when connecting to “PowerBI dataset”.

    The setup of my PowerBI is as follow:
    – PowerBI model – 1 file with all the tables linked to SQL Server (eg. Model.pbix)
    – PowerBI reports – 10 files connecting to the same PowerBI model (ie. Model.pbix) by using “PowerBI dataset” (eg. ReportA.pbix, ReportB.pbix, ReportC.pbix…..etc)

    I was trying to convert one of report to PBIT file and also convert the PowerBI model to VPAX file like this:
    -> Model.pbix ==> converted to Model.vpax
    -> ReportA.pbix ==> converted to ReportA.pbit

    Then I used your tool to import the above 2 files and hit refresh, but the operation was failed and it says some refresh errors about “loading blocked by failures with other queries”. I am not sure if the tool can still be used if I split the model and reports in separate files?

    I also tried to convert the PowerBI model to PBIT file and and also convert the same PowerBI model file to VPAX and it was successful (it took almost 5 hours to complete the full refresh):
    -> Model.pbix ==> converted to Model.vpax
    -> Model.pbix ==> converted to Model.pbit

    Based on my settings with “PowerBI dataset”, am I allow to use your tool for analyzing my reports?
    Thank you!

    Best regards,
    Emily

    Reply

    • Hello Emily,
      currently you can only use this tool on one file. So not with splitted files like yours, unfortunately.
      /Imke

      Reply

  20. Hi Imke I have found the issue with my problem “cannot convert null to text”. The issue occur when the dataset contains a Role in RLS without any condition. The table for roles will fail for that row with the error. I have added a replace error in the middle but this might help you reproduce the error to build a even better script 😉
    Hope that helps 😛

    Reply

  21. Hello Imke, When loading data I get this error message:

    Calculation Items
    Loading blocked by failures with other queries.
    Columns
    We found extra characters at the end of JSON input.
    Columns Segments
    Loading blocked by failures with other queries.
    Columns Hierarchies
    Loading blocked by failures with other queries.
    Measures List
    We found extra characters at the end of JSON input.
    Relationships
    Loading blocked by failures with other queries.
    Roles Expressions
    Loading blocked by failures with other queries.
    Tables
    Loading blocked by failures with other queries.
    User Hierarchies
    Loading blocked by failures with other queries.
    FieldUsageDetails
    We found extra characters at the end of JSON input.
    WhereUsed
    Loading blocked by failures with other queries.

    PBI version is2.87.762.0
    Dax Studio is 2.13.1
    Could you help, please

    Regards
    Andrea

    Reply

  22. Hi Imke. I’m using your Cleaner tool. Really works well. Thanks a lot for this! But I do have a question.
    If I’m using column [COL1] in Power Query (so not DAX) to create another column [COL2] in the table and I don’t use the first column [COL1] anymore in the report, will it be reported as “not used”? Will the PowerQuery still be working ok if I remove [COL1]? Or does it depend on when I do that?

    Thanks a lot!
    Marco.

    Reply

  23. Hello Imke.

    Thanks for the help, but the whereused column is not working in the PowerBICleanerV7 version.

    Metrics are being used and the column is empty.

    Reply

  24. Hi Imke,

    Thanks for the amazing tool you built. I’ve used it a couple of times so far and it has been really helpful for cleaning up some files with more than 700 measures.
    I’m now trying to do the same with a similar file but having some trouble to refresh your template after changing the parameters.
    Apparently the problem comes when uploading “FieldUsageDetails” and “WhereUsed” tables.
    Do you have any pointers of why can this be or what can I try to fix it?

    Cheers,
    Joaquín.

    Reply

    • Hi Joaquin,
      please check out he new version that I’ve just uploaded.
      If it still doesn’t work, please send me your pbit and vpax file for further investigation.
      Thanks and cheers, Imke

      Reply

  25. Hi just wanted say many thanks, this is a very useful tool, thanks for share it with the community. I also had to removed the role level security filters and worked as intended.

    Reply

  26. Hi,
    Maybe I’m missing something obvious but there doesn’t appear to be a PowerBICleanerV10 PBIX file within the PowerBICleaner.zip file linked in the article.
    If it’s there, could you advise of the path to the PBIX in the Zip file?

    Reply

  27. Hi Imke, thanks very much for this tool, very helpful! However, it seems that fields that are used in tables (not matrices) aren’t picked up by it. Could you please verify or point me towards a solution? I unfortunately cannot share my pbit/pbix files 🙁

    Reply

  28. Hi Imke, thanks so much for this awesome tool! I’ve been using it for documentation purposes since last year and the output has been consistently amazing. Seems like compared to the previous versions, v10 runs much faster and the visual titles are captured more accurately (at least on the pbix file I’m running). I may have missed some details though, but is there a link where I can find additional information on the version updates? I’m curious to know how v7 differs from v10

    Reply

  29. Hi Imke, thanks so much for Power BI Cleaner!
    Could you please tell me where I should find the cause of the problem with the missing “Value” column and how to solve it?

    Calculation Items
    Loading blocked by failures with other queries.
    Columns
    The column ‘Value’ of the table wasn’t found.
    Columns Segments
    Loading blocked by failures with other queries.
    Columns Hierarchies
    Loading blocked by failures with other queries.
    Measures List
    The column ‘Value’ of the table wasn’t found.
    Relationships
    Loading blocked by failures with other queries.
    Roles Expressions
    Loading blocked by failures with other queries.
    Tables
    Loading blocked by failures with other queries.
    User Hierarchies
    Loading blocked by failures with other queries.
    FieldUsageDetails
    The column ‘Value’ of the table wasn’t found.
    WhereUsed
    The column ‘Value’ of the table wasn’t found.

    Thank you in advance!

    Reply

    • Hi Elizaveta,
      are you able to share your pbit and vpax files for me to investigate?
      info at thebiccountant.com
      Thx!

      Reply

  30. Pingback: Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity – Olivier Travers

  34. First of all, thank you so much for taking your time to create this App.

    “Elizaveta16.03.21 @ 6:54 PM
    Hi Imke, thanks so much for Power BI Cleaner!
    Could you please tell me where I should find the cause of the problem with the missing “Value” column and how to solve it?”

    I am having the same issue at the PowerBICleanerV11_2_p version.

    Do you have any idea about how we can solve it?

    Thank you again.

    Reply

    • P.S.: I noticed that the problem is exactly at the ‘UsedFields1_Filters’ table, there are some errors on the ‘Applied steps’ at that table that make the column Value disappear.

      Reply

  35. For the folks hitting the refresh error “The column ‘Value’ of the table wasn’t found”, Cha on our team found one trigger for this is if your source PBIX file has no Page-level filters on any page. A workaround that got a successful refresh of Power BI Cleaner was to add a Page-level filter to our source PBIX file.

    We’ve shared our files with Imke and she is looking at a fix.

    Reply

    • Hi Mike, thank you for your interest. In my case the source PBIX has a page-level filter at the page called ‘Tables’. I set WhereUsed[Details] filter on the rest of them and still hitting the error related to the colum [Value].

      Reply

      • Hi Thiago, did you apply the page-level filter on your original PBIX file, not the Cleaner file?
        1. Original / Source PBIX file – We edited our source PBIX file with a page-level filter first, then ran the vpax and saved the pbit file afterwards.
        2. Cleaner File – We used these 2 new files as input to the parameters, before hitting the refresh button.
        Everything seemed fine on our end.

        Reply

  36. Hi Imke tool is very user full but i face problem in refresh

    Calculation Items
    Loading blocked by failures with other queries.
    Columns
    We found extra characters at the end of JSON input.
    Columns Segments
    Loading blocked by failures with other queries.
    Columns Hierarchies
    Loading blocked by failures with other queries.
    Measures List
    We found extra characters at the end of JSON input.
    Relationships
    Loading blocked by failures with other queries.
    Roles Expressions
    Loading blocked by failures with other queries.
    Tables
    Loading blocked by failures with other queries.
    User Hierarchies
    Loading blocked by failures with other queries.
    FieldUsageDetails
    We found extra characters at the end of JSON input.
    WhereUsed
    The column ‘Value’ of the table wasn’t found.

    Reply

  37. Hello Imke,

    I love your Power BI Cleaner tool. Very useful when dealing with some messed up data models and trying to reduce memory usage. Thanks for making it available!

    But right now I’m having the same problems Ivan Mihajlov described on 4/26/21 5:29am.

    I downloaded the version from the article above. Did I get the wrong version or is there currently a bug regarding newer .vpax files.

    Hope to see you again in the near future.

    Uwe

    Reply

  39. How did I never use this before? I don’t know but I just tried it last night and it’s now one of my favorite tools. Right up with DAX Studio. Great work!!! This will save SO much time reducing someone else’s report.

    Reply

  40. Hi there.
    I was really looking forward to use your tools BUT, no matter what i do i keep on getting this (source is Azure SQL tabel):
    Calculation Items
    Loading blocked by failures with other queries.
    Columns
    The column ‘Value’ of the table wasn’t found.
    Columns Segments
    Loading blocked by failures with other queries.
    Columns Hierarchies
    Loading blocked by failures with other queries.
    Measures List
    The column ‘Value’ of the table wasn’t found.
    Relationships
    Loading blocked by failures with other queries.
    Roles Expressions
    Loading blocked by failures with other queries.
    Tables
    Loading blocked by failures with other queries.
    User Hierarchies
    Loading blocked by failures with other queries.
    FieldUsageDetails
    The column ‘Value’ of the table wasn’t found.
    WhereUsed
    The column ‘Value’ of the table wasn’t found.

    I am using your latest version.

    Reply

  41. Hi, thanks for your great job!
    Have the same problem
    “We found extra characters at the end of JSON input.”

    in my case PQ looks
    “DataFormat.Error: We found extra characters at the end of JSON input.
    Details:
    Value=@
    Position=4251″

    in JSON this looks like:

    … {\”value\”:\””@5= …..

    Reply

  42. Hi again.
    In this version you have made 2 parameters. The first one in asking for port number for localhost.
    I am not using local host but AAS. Moreover, you have hardcoded localhost into you reports connection so there is no other options to connect to AAS as we do.

    Therefor i cant use it (and want to real bad ).

    What should i do? any other ways?
    Tnx in advance
    Br.
    Yair

    Reply

  43. Followed all of the directions, but when I click refresh all the procedure fails with the error message: Columns
    The column ‘Value’ of the table wasn’t found. Same error for Measures List, FieldUsageDetails & WhereUsed.

    Any ideas what went wrong and how to fix it?

    Reply

Leave a Reply

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