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.

What can go wrong?

Getting an error message: ..”The field “model” of the record wasn’t found”?
This will occur if you haven’t transformed your pbix to pbit like described above.

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 (29) 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

      • Hello,

        I have the same problem when working with the latest versions of Excel and Power BI. Any thoughts about the reason of this issue?

        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

  7. This is a great feature as right now it’s a night mare to deal with power bi file(pbix) especially in a setup where multiple people are working on reports/tabs simultaneously. However, looks like your tool only works with non live connection. Do you have any idea on how to do this(comparing) in a live connection environment? We are really struggling with issues of reconciling changes made and lot of finger pointing is a common sight in our environment.

    Appreciate any help.

    Reply

  8. Great tool, Thank you very much!

    I downloaded your template and tried comparing two Powerbi files. All the tabs in your template got refreshed except “CompareTables” tab. This tab seems to be failing for me as the data source “TablesNew” & “TablesOld” fails to load. Advanced editor shows error “Expression.Error: The field ‘model’ of the record wasn’t found.”

    its happening in the underlying M query when it tries to access Model as below.

    let
    Source = AuditPBIT_New,
    Content = Text.Clean(Text.FromBinary(Source{3}[Content])),
    Custom1 = Json.Document(Content),
    model = Custom1[model],

    Not sure if anything is wrong with my file. It would be great if you could help on this. Thank you.

    Reply

    • Hi Alex,
      you’ll get this error message if you haven’t transformed the pbix to a pbit file.
      /Imke

      Reply

  9. Thanks for this, Great tool.

    I downloaded the template and found that the “CompareTables” tab is failing to refresh. It seems to be due to the issue shown in TablesNew & TablesOld sources. It is showing “Expression.Error: The field ‘model’ of the record wasn’t found.” Is there any reason why it is failing for me? It would be great if you could help on this. Thank you very much.

    Reply

    • Hi Muthu,
      you’ll get this error message if you haven’t transformed the pbix to a pbit.
      /Imke

      Reply

  10. how do I get around this
    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 don’t see an option to disable formula firewall

    Reply

  11. made it past the firewall stuff then got an error [Expression.Error] We cannot convert the value null to type Record; but got results

    Reply

Leave a Reply

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