Posts in Category ‘M’

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:

Read more

Text.RemoveBetweenDelimiters function for Power BI and Power Query

While Power Query has a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the optional 4th parameter. (“Text.RemoveBetweenDelimiters “- function)

The code for the Text.RemoveBetweenDelimiters -function

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

A new Table.ContainsAnywhere function for Power Query in Power BI and Excel

The native Table.Contains-function in Power Query tells you if one or more strings are included in one or more of its columns. But you have to be specific about which strings you search in which column. But what to do if you want to search a string in all of its columns instead? Use my new Table.ContainsAnywhere function.

Problem

In the native function, you have to pass in a record with search term and column name. So if you search for “blue” in column “Description”, your formula would look like so:

Table.Contains( YourTableName, [Description = "blue"] )

But that’s not what I want in this case. I want the formula to search through all columns within the table for the occurrence of “blue”.

Solution

Read more