Microsoft has released the „field parameters“ feature in the latest Power BI Desktop. This allows the user to change the measures OR dimensions of a visual without further modeling by clicking on a slicer. I introduce this feature in this post and show how you don’t have to choose measures OR dimensions, but can have BOTH at once ? What are Field Parameters? As of today (May 16th, 2022) Field Parameters are a preview feature in Power BI Desktop. It allows users to change the measure or the dimensional attributs within a  report, by clicking on a slicer. Because it’s still in preview, make ..read more

Normally I calculate sums in Power Query over rows. Recently, however, I was given the task of calculating sums over columns. I wrote a German-language post about this here and used mainly functionalities of the UI. Power Query guru Bill Szysz has commented this post (even though it was in German) and sent me another alternative solution via email. He gave me his permission to share this solution with you here. In addition, I already had a conversation with my friend and Power Query guru Imke Feldmann about another alternative solution, which I have also included in this article. Happy learnin ..read more

A couple of  weeks ago I could finally publish my post about tables in M – how, when and why. Under „Other special table functions“ I mentioned the function Table.Profile() as a function to get meta information about tables. During my research I noticed that this function accepts a second optional parameter which is not documented anywhere. Also other posts about this function, which colleagues from the community had already written, did not mention this parameter (at least none I could find). This made me curious, so I reached out to the Power Query dev team. Syntax of the function The s ..read more

I recently came across a behavior of the Table.ColumnsOfType() function that I want to share with you today. Since this post talks about ascribed types in M, I recommend that you read this post first if you are not familiar with these topics yet. Preface The function Table.ColumnsOfType() allows you to retrieve a list of columns that match specified types (second argument) from a given table (first argument). This can be very helpful, for example, if you want to select all text columns of a given table. The syntax of the function is as follows: Table.ColumnsOfType(table as table, listOfTypes a ..read more

Unlike most of my articles, this one doesn’t solve a problem, it highlights one. If you use Microsoft Access as a data source to import data via Power Query, this article should make you aware of a limitation when importing so-called ‚multivalued fields‘. What are multivalued fields in MS Access? ‚Multivalued fields‘ are a special feature of MS Access (SQL Server does not have this feature). These allow to store not only one value but up to 100 values in one field of a single record. Click here for examples and to learn how to create them. What if you want to import those fields with Power Que ..read more

At the time of writing, the Power BI service supports 44 different languages. In this article I will show you how you can significantly reduce the effort to translate your tables, columns and measure, while you can do the actual translation in tabular form in an Excel file. Expectation management To clear up any misunderstandings right at the beginning: No, my tool does not translate your data model by itself. You have to translate the names of tables, columns and measures yourself. But in this post I provide you with a practical solution that reduces your manual effort in Tabular Editor to a ..read more

Before an analyst can start analyzing the data, first it is necessary to know whether the data is up-to-date or not. There are (at least) two pieces of information relevant: 1) When was the data model last updated and 2) Is the data in the data source up to date. With this post I want to show how I like to let my users know how up-to-date their data is. How I like to present this information I think the information when the data model was last updated is probably the most important. I like to place this information directly on the report so that it is immediately visible and, in case the repor ..read more

One of the fastest methods I know to create tables in Power Query/ M is the function Table.FromColumns(), which takes a list of lists and creates a table from them. This way you can easily create a calendar table for example. In this article I will show you how you can use the metadata record of list items to flexibly assign both column names and datatypes to the created table. Take a look at the following M script. At the beginning I define 4 lists: A list of… dates, day names, month names and month numbers. The ListOfColumns is basically a compilation of the lists defined above, which are ..read more

I can recall that back then, in the good old Excel VBA days, at some point, I was very busy with error handling to make it easier for the user to understand the logic of my program. One article was particularly illuminating for me: Error handling via an error class, by Dick Kusleika. The languages M and VBA don’t have much in common, but also in M I want raise custom errors, when if something contradicts the business logic. Providing the user with a meaningful error message can greatly increase the acceptance of the program. This article deals with how you can gracefully handle custom err ..read more

If you deal with Time Intelligence functions (TI) in DAX, you quickly reach the point where you can no longer use the standard TI functions. Very quickly you reach the ingenious site (daxpatterns.com) of Marco and Alberto from SQLBI, where they among other things show patterns for custom TI functions. The current article uses the TI pattern as an example to show how the filter context works, but this can generally be transferred to the functionality in DAX. How the pattern looks like Custom TI functions can be useful and necessary if the standard TI functions of DAX are no longer sufficient. T ..read more