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 😉

Export large amount of data from Power BI desktop visuals

I’m going to show how to export data from visuals in Power BI Desktop that’s too big to be downloaded by the native functionality and therefore returns this error-message:

Export data from visuals

Check if you really need this

Although the method is fairly simple, there are simpler methods if you just need the raw data from your data model (and not the specific aggregations or measures that the visual contains): Read more

Debug DAX variables in Power BI and Power Pivot

When you’re dealing with a beast like DAX you can use any help there is, right? So here I show you how you can debug DAX variables who contain tables or show the result of multiple variables at once. So you can easily compare them with each other to spot the reason for problems fast.

Please note, that currently only comma separated DAX code is supported.

Example

Watch this measure from Gerhard Brueckl’s brilliant solution for dynamic TopN clustering with others. It contains 5 variables who return tables and one variable with a scalar:

Measure with variables who contain tables and scalars

If you want to follow along how this calculation is evolving for each value in a matrix, my VarDebugMeasure will show details of every variable like so:

Measure to debug DAX variables

Method

Read more

DAX CALCULATE Debugger

CALCULATE is the most powerful function in DAX, as it allows you to change the filter context under which its expression is evaluated to your hearts content. But with big number of options to choose from, often comes big frustration when the results don’t match expectations. Often this is because your syntax to modify the filter context doesn’t do what you’ve intended. Unfortunately CALCULATE only displays its result and not how it achieved it, so debugging becomes a challenge. This is where my CALCULATE Debugger measure can help out:

DAX CALCULATE Debugger

This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 😉

Read more

The full Table.ContainsAnywhere function for Power Query in Power BI and Excel

In a previous post I introduced the concept of a function that searches for an occurrence of a character or string within all columns of a table. Here I share the full “Table.ContainsAnywhere” – function with parameters for many useful options.

Function parameters and options

  1.  The first parameter “MyTable” refers to the table to search through
  2.  The 2nd parameter “MySearchStrings” can be either a text field or a list of strings to be searched for. The function will take care of any of these cases automatically.
  3.  If the 2nd parameter is a list and this 3rd parameter is null or not speified, the function will return true if any of the list items is found within the table. But if set to “All”, all list items have to be found somewhere in the table for the function to return true.
  4.  By default, the search will be made in a case sensitive mode (as this is the default-mode in Power Query). But any entry into the 4th function parameter will turn this to a case insensitive mode instead.
  5.  By default, the string or list entry has to match fully with any entry in the table. Again, any entry in the 5th parameter swaps that to a partial match.

Read more