Compare Power BI files with Power BI Comparer tool

Have you ever wanted to compare a version of a Power BI file with a previous one? … In the unlikely case that you haven’t yet, just wait until the auditors are in again – my new Power BI Comparer tool will save your day then 😉

Or maybe even before you uploade a new version of a report that has already been published to the service. How do you communicate the changes to your colleagues? Wouldn’t it be nice to have a tool that performs that comparison and documentation fully automagically?

Power BI Comparer

Fortunately my Power BI Comparer-tool makes it super easy to compare all properties of 2 Power BI files with each other: Just convert your pbix-files to pbit (as we need to access the data model properties as well) and drop the paths to these new files in my Excel-file like so:

Fully specifying file path in Power BI Comparer

Then hit Data -> Refresh All (make sure that privacy levels are disabled)

Disable privacy settings for the Power BI Comparer Tool

Et voila: The first page of the report shows the total number of changed items in a small summary table:

Summary of the differences between the Power BI files

Just hop to the sheets with changes and either filter on changed items directly or use Ctrl+ arrow-keys in the “Change”-column to see the changes in their original context:

Yellow coloring In addition to the “Change” column

I prefer navigating with Ctrl + up- or down-arrows, as often the keys of the rows themselves don’t contain enough information and very often one finds helpful information the rows above the changed items.

Comparison sheets

  • In the column “Change” you see if an item has been changes, added or deleted.
  • The “Key” column contains the full path of the value within the JSON-file. For list items, I tried to fetch the name of the following property. That’s actually a bit tricky and could lead to duplicates. In such a case, you’ll get a warning on the first page. If you send me your pbit-file, I’ll adjust the tool accordingly.
  • The columns left to the “Key” column are made for easier navigation and you can use slicers on them.
  • The “Property” column holds the name or the property to which the Values belong to. (That’s also the last element of the Key)

Changing keys

If you change the name of a query (table), measure or column, this will not be recognized as a change (of name), but as a deletion of the old element and the creation of a new one. That’s one of the reasons why I made this tool in Excel, as you can easily bring these information together and perform a manual comparison (if eyeballing isn’t sufficient) instead.

What’s not covered?

Nothing. The comparison includes everything from the pbit-files: So beneath your M and DAX code, you’ll see all about your visual definitions (incl. filters set !), row level security and much, much more. Actually, I found some information a bit noisy (like many date fields, telling you when which changes happened). So I filtered them out in Excel. I’d recommend to check it out and play a bit with it to find the most suitable settings for you.

However, if you find a bug or missing features, please add a comment or send me a message.

Download the file here: PowerBIComparer_Upload.xlsx

Latest version: 1_1 (15th September 2019)

Enjoy and stay queryious 😉

Comments (17) Write a comment

  1. Thanks for this excellent tool, it’s exactly what I’ve been searching for. And it’s very well done.

    Just one minor issue I have with it. When I enter the names of the two pbit files and click on Data->Get Data->Query Options, I get a “Compatibility Warning” saying the queries “might be incompatible with your current version of Excel”. (I’m running a fully updated install of Office 365 ProPlus, so I don’t think that’s the problem.)

    When I dismiss this and confirm the Query Option settings, then click Data->Refresh All, I get a red cell with “!! Inconsistency Warning !!”. It does create the comparison on the remaining tabs, and everything looks good there.

    Anyway, very nice job and extremely useful. I’m looking at including it in our version control and release procedures.

    Thanks again.

    Reply

    • Hi Sam, thanks for the heads up.
      The compatibility warnings can be ignored once the queries return result.

      With regards to the duplicate warnings, you should be more cautious, as they could create false alarms (flagging differences that aren’t any).
      This is probably because you’ve used some items that I haven’t in my tests and they contain elements that should be handled differently. So if you don’t mind, please send me your pbit files and I can include the necessary adjustment in the next release update. (info ‘at’ thebiccountant.com)

      Thanks a lot and kind regards,
      Imke

      Reply

  2. Imke, thanks for your prompt response. I will send the files via email.

    Once I used it on a couple of comparisons, I found things I never new happened as a result of the simple change I made. For example, deleting a parameter with datatype datetime deleted a date template and localdate table I didn’t know existed – I thought those were only created for queries with date columns.

    The tool provides great information about the differences between two reports. Exactly what I need. Thanks again for this tool.

    Reply

  3. Love the comparer tool! Did not know the PBIT file was zipped and had that content!

    I have question that is not in scope for this post, but I am looking for methods of automating Power BI report data testing. I have PBI reports with multiple worksheets, with multiple viz on each. I would like to access each viz data in some automated fashion to test data. Anyone seen anything that might take me in this direction?

    Reply

  4. This is really good – great for understanding the content of PBIX files!!

    I really like it!!

    Reply

  5. Hello,
    Thank you for your useful tool ! It will save me lot of time ! Actually, I need your help because an error occurs when, after I entered the pbit files absolute path, I refresh the data :
    “Formula.Firewall: Query ‘AuditPBIT_Old’ (step ‘Added Custom’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
    I use Excel 2013 and PBI Desktop 2.69.5467.5201 (May 2019):
    Thanx in advance for your help !

    Reply

    • Hi Adel,
      in the current version you have to disable the formula firewall. This will be changed in the next version.
      /Imke

      Reply

  6. Hi (by the way what is your name ? 😉
    Thank you very much for your answer.
    I´ve just noticed that I missed the point of ignoring Privacy level in the excel workbook. I did it and now I don´t have the previous error message anymore. Nevertheless, I have a new one :
    Expression.Error: The import List.Zip matches no exports. Did you miss a module reference?
    Thanx in advance for your help !

    Reply

    • Unfortunately I haven’t seen this error-message here before.
      Are you using pbit (and not pbix)-files?
      /Imke

      Reply

Leave a Reply

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