Tidy up Power BI models with the Power BI Cleaner tool

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 one exception actually: Fields used in the definition of incremental load policies are currently not identified. 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)

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

Warning

Custom visuals with bad metadata could lead to error.
Will upload hardened version soon.

Download file:  PowerBICleaner.zip

Enjoy and stay queryious 😉

Comments (20) 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

Leave a Reply

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