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: 2_7 (21 November 2020)
Enjoy and stay queryious 😉
Nice Imke
Thanks Matt 🙂
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.
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
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.
Thanks Sam!
Yes, sometimes there is a lot going on in the background 😉
Very cool! I can’t wait to try this. Thanks!
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?
Thanks Curtis, but I’m not aware of such a tool
This is really good – great for understanding the content of PBIX files!!
I really like it!!
Thanks Neil,
very pleased to hear that!
/Imke
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 !
Hi Adel,
in the current version you have to disable the formula firewall. This will be changed in the next version.
/Imke
Hi! I have the same problem with the latest versions of Power BI and Excel. What can be a problem?
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?
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 !
Unfortunately I haven’t seen this error-message here before.
Are you using pbit (and not pbix)-files?
/Imke
Hi Imke,
Yes I´m using pbit files
Unfortunately no further idea here.
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.
If you try to work with multiple people on one pbix-file, then it’s time to spin up some serious version control solution like described here:
Thank you thank you lmke! You made my day with this tool!!
Great to hear, so thanks for letting me know 🙂
/Imke
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.
Hi Alex,
you’ll get this error message if you haven’t transformed the pbix to a pbit file.
/Imke
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.
Hi Muthu,
you’ll get this error message if you haven’t transformed the pbix to a pbit.
/Imke
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
made it past the firewall stuff then got an error [Expression.Error] We cannot convert the value null to type Record; but got results
When i enter the new parameters for my files i get this error when applying changes – error is “FieldUsageDetails
OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E.” Any ideas?
No, haven’t come across this error message.
But you can find a lot on the internet about it.
/Imke
Hi, it’s really cool tool and a great idea – thanks a lot
We’ve got an error:
[Expression.Error] Ein Wert vom Typ “Record” kann nicht in den Typ “Text” konvertiert werden.
english could be:
[Expression.Error] A value of type “Record” can not be converted to type “Text”
is there a easy way to find to column which causes the error?
Thanks a lot,
Magnus
Sorry to hear. Are you able to share the pbit file(s) so I can look into it?
Actually: Are you using the new enhanced metadata format? Unfortunately this tool will not work with it.
/Imke
Hi Imke, thank you for your hint, but we do not use the new format. I am going to holidays for two weeks now – I will look at it when I returned
Thanks a lot
Thanks for sending the file, found a bug and updated the download.
/Imke
Thank you so much for your solution and support – great work 🙂
Very nice work!
Thank you!
Hello, Running with warning and errors
Initialization of data source failed
Hi Ashish,
Sorry about it. This is due to the new changed metadata format.
Trying to fix this asap.
/Imke
Hi Imke,
I would really love to use this tool. However, I am getting four of these errors:
Expression.Error: The key didn’t match any rows in the table.
Details:
Key=
FileName=DataModelSchema
Table=[Table]
Hi Ed,
sorry to hear. Are you using the newest Desktop versions?
Otherwise: Are you able to send me your pbit files for further investigation?
Thanks, Imke
Thanks so much for this tool – just what I was looking for as I develop a versioning/documentation system! It is an excellent contribution to the community!
Can I delete the two queries ReportLevelFilters and ReportLevelFilters(2), since they are throwing errors and seem to have no dependents?
Also, it might be nice to be able to add comparisons of Dataflow json exports – this the only area of my development pipeline that is not covered by the tool.
Pingback: Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity – Olivier Travers
I get an error when I try to use this. I get [Expression.error] The field ‘expressions’ of the record wasnt found.
I still get the error even after converting to to templates. However, I am using direct query…would that make a difference?
In the mash-up powery query…I get this.
Expression.Error: The field ‘expressions’ of the record wasn’t found.
Details:
culture=en-US
dataAccessOptions=
legacyRedirects=TRUE
returnErrorValuesAsNull=TRUE
defaultPowerBIDataSourceVersion=powerBI_V3
sourceQueryCulture=en-US
modifiedTime=2021-07-14T17:48:35.676667
structureModifiedTime=2021-07-14T18:10:53.293333
Hi Imke,
The tool is really nice! For example, Here we are getting the information using the .pbit file but what if I have created the queries in Excel’s Power Query what could be done in that scenario.
Thanks,
Ajinkya K
I am asking this question only in context to M queries Comparison.
Hi Imke,
How to check the data types of columns in Power Query (Specially about how to detect “Any” data type)
Thanks,
Ajinkya K
i have been trying to use the excel for the comparison of two Pbix files which are live connected, i have all the instructions which have given above in order to perform the comparison of two files, however when i click on refresh all i am getting an error “Could not find a part of path” please need assistance on the same, want to fix the error and use the comparer tool error free.
Hi Anil,
sorry to hear about the trouble.
Where is your data stored, in a local drive or in the cloud?
BR, Imke
Hi, thanks for the tool. I’ve saved the reports as template (pbit), but I get the error: Expression.Error: The field ‘expressions’ of the record wasn’t found. Despite the error, “CompareVisuals” and “EverythingElse” have been populated with new data, it seems they have been correctly updated. “CompareMashup” shows data not coming form my files (DimProductCategory2 etc.). “CompareTables” shows content coming from my files (for instance in the Name column I can select the names of my tables), but the table itself is empty, it only shows the column names. Does it mean that there are no diffrence?
Update: “CompareTables” is not empty, I simple needed to scroll up :-). It seems the only problem is in “CompareMashup”
Pingback: Tidy up Power BI models with the Power BI Cleaner tool – The BIccountant
The solution looks really cool. But unfortunately I seems only to work with certain types of Power BI files. I failed with a template created from a Power BI Book using a live connection to an SSAS tabular database.
Is there a way, that at least the comparison of the visuals is usable ?
If required, I can sent you a copy of my dev/test template
According to further investigation it seems, that the handling of special characters in the name of attributes (layout/report) also cause a failure in the interpretation of the json fragments of the specific power queries for report layout:
DataFormat.Error: We found extra characters at the end of the JSON input.
Details:Value=% Position=75830
FYI: I use also following special characters in the naming of attributes:
/* Char: ΔΣ↑%∑∆←↓→Ø○◌ */
I’ve found the solution 😉
The issue is within the code page setting before resolving the json content.
EXAMPLE query: RL_staging_Old
let
Source = AuditPBIT_Old,
Content = Source{[FileName = “Report/Layout”]}[Content] ,
#”Imported JSON” = Json.Document(Content,1200)
/*
Custom1 = Text.Clean(Text.FromBinary(#”Imported JSON”)),
#”Parsed JSON” = Json.Document(Custom1)
*/
in
#”Imported JSON”
thank you so much for this! a really effective way to do this without ALM toolkit.
it appears a recent PBI change resulted in no longer needing to split the M by #(lf), so this step can be removed in that part of the query to allow a refresh to take place.
Hi Imke
I’m getting some ‘Cant convert a value of type list to type text’, eg. in table DataMashup_OldT ?
Regards, Henrik
I’m glad to find this tool here. My question is, is the tool safe to use? Any concerns about the data security?
For those facing the ‘Can’t convert a value of type list to type text’ error, it seems like the structure of the PBIT file may have changed a little since this tool was written and the queries in the PBIT file are now being returned as lists instead of text. The following (fairly inelegant) solution seems to fix the issue:
Using the Advanced Editor, in both the DataMashup_Old and DataMashup_New queries, replace everything after the line beginning ‘QueriesFromPartitions…’ with the following:
#"Removed Columns" = Table.RemoveColumns(QueriesFromPartitions,{"type"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "expression1", each Text.Combine([expression],"#(lf)")),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"expression"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"expression1", "expression"}}),
Custom2 = #"Renamed Columns" & QueriesFromExpressions,
#"Removed Duplicates" = Table.Distinct(Custom2, {"name"})
in
#”Removed Duplicates”
What we’re doing is just converting the list back to text using a line feed separator.
Hope that helps someone!
Ben.
I love this tool! Do you have a legend that would explain how to interpret the CompareVisuals page, especially the Key, Property, and Index columns?
Hello Team,
Really a good tool. But when i try to refresh the data, it gives me the below error message.
[Expression.Error] The field ‘expressions’ of the record wasn’t found