Table.Group: Exploring the 5th element in Power BI and Power Query

In this post I’ll show you the magic stuff you can do with the 5th parameter (the optional comparer function) of the Table.Group M-function in Power BI and Power Query:

Table.Group parameters

  1. table as table,
  2. key as any,
  3. aggregatedColumns as list,
  4. optional groupKind as nullable number,
  5. optional comparer as nullable function

If you’re not familiar with the 4th parameter (groupKind) already, I strongly recommend to read Chris Webb’s article, as we will build on its knowledge here.

Another aspect worth mentioning for the modus in GroupKind.local is the performance aspect: It runs MUCH faster for large datasets than the default-setting. So if you are sure that your data will always be sorted accordingly, you can speed up your grouping-operations considerably. That means: Your data has to be sorted correctly by default. At least for my tests, you would loose the performance-gain once you’d sort your table by an explicit step before.

You can find an overview of comparer functions here.

Case insensitive grouping

Imagine there was a twist in Chris’ dataset and it would look like so:

Table.Group – Modified Source Data

We would probably not be happy with these results then:

Table.Group Problem with Case Sensitivity

Because M is by default case sensitive, we get more groups than we want. Let’s try Comparer.OrdinalIgnoreCase to the rescue then:

Pretty neat, isn’t it 😉  (You can use that comparer in other functions as well, see here for text- and list operations)

Something like this was what I’ve showed Huang Caiguang the other day, who asked me what the 5th parameter of this function was about (or so I understood). He then sent me a link to one of his articles, which demanded a good 2 hours for me to digest and understand: We can also use custom functions to create all different sorts of grouping behaviours here. These are my 2 favourites:

Read more

Import text from pdf files in Power BI

While Power BI will soon provide functions to import tables from pdf-files, there might be occasions when you actually need to import text from pdf files (in unstructured form). With a little help from R in Power BI you can do exactly that. (And don’t worry: No need to learn R here: The necessary R-code is already included in my function below. All you need is to have R installed your machine). Please also note that at the time of writing the refresh of these queries in the service is only supported with the personal gateway and not with the enterprise version.

Prerequisites

You can use the function below just like a normal M-function, just pass the (URL- or file-) path to it. All you have to take care of is that a instance of R is running on your machine. If this is new to you, check out Ruth Pozuelo’s video showing all the necessary steps: How to install R for Power BI

There is one package required: pdftools. The video above also shows how to install it.

Function

Import text from PDF files:

You can try calling this function for a pdf-file from the internet like the M formula language specification like this:

ImportPdfText(“http://download.microsoft.com/download/8/1/A/81A62C9B-04D5-4B6D-B162-D28E4D848552/Power%20Query%20Formula%20Language%20Specification%20(October%202016).pdf”)

If you want to import local files from your computer, just paste the full file-path instead of the URL. You don’t have to care about the direction of the slashes, both versions (forward and backwards) are accepted.

How to use

The script will return a table with one row for each page in the pdf-file by default. But it has an optional 2nd parameter that will return one row per pdf-text-line instead, if you put 1 into it. A page index and a row index will help navigating the result.

The 3rd parameter is an optional owner password for the pdf and the 4th the optional user password. If you’re using them, you have to enter null for the previous optional parameters. The following example shows how to use a user password while leaving the others “empty”:

ImportPdfText(“MyPdfPath”,null, null, “MyPassword”)

Enjoy & stay queryious 🙂

Remove repeating characters from a string in Power BI and Power Query

Repeating spaces often cause problems when cleaning up your data. My new function “Text.RemoveRepeatingCharacters” can come to the rescue here.

Imagine you have a table like this:

Challenge

To further work with this data, it would often be best if there was just one space between the words and not many.

The following function will do this for you:

Function Text.RemoveRepeatingCharacters

How to use

It takes 2 arguments: The Text/string and the Delimiter. So for our example you have to enter a space, but you can use any sign there for other use cases as well.

How it works

It splits the text up into a list using the delimiter from the 2nd parameter (4: TextToList). Where one delimiter directly follows another, the element in the list will be empty. The next step (5: FilterList) then filters the list and removes these empty fields. In the last step (6: Result) the remaining (non-empty) fields will be reassembled, using the delimiter again. That way, just one delimiter will be left.

Enjoy & stay queryious 🙂

Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query

Today I’m sharing a handy function with you that allows you to retrieve all or just a couple of dates between 2 given dates: Date.DatesBetween.

Usage

This function takes 3 parameters:

  1. From- or Start-date
  2. To- or End-date
  3. A selection of ONE of these intervals: Year, Quarter, Month, Week or Day

All dates will be created at the end of the chosen interval: So if you want to analyse events with a duration for example, where you want to transform your data to show one day per (monthly) event, this function generates month-end-dates for every month within the timespan. Please not that if the To-/End-date is within a month, the last element of the list will NOT be that day, but the day of the end of that month.

The default-value for the 3rd parameter is “Day”, so if you omit the specification, the function will return a list of all days in between.

Read more

Right Aligning Text in Power BI: Format Improvements for Easy Profit&Loss Reports

Edit 14-Dec-2017: Now that we can right-align text measures in PowerBI, a SWITCH-measure like here: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/#comment-719 is the best alternative in my eyes. No need to read on 🙂

As shown in my last part of the Easy P&L-series, Power BI unfortunately still lacks some fundamental formatting options like:

  1. Right aligning text (please vote for it here: Right align text in Power BI – edit 15th Nov: We’re there: Right aligning text is available now: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/)
  2. Display numbers in different formats within one column (either to be implemented as a “neutral” format for Switch-measures, where the referenced measures carry the formatting attributes already, or as a part of a formula-based conditional formatting) (Thanks Matt for the voting-link: Conditional format SWITCH measure)

So for the moment I choose between the following workaround-options:

  1. Display %-values in a separate column
  2. Format numbers as text and fill up with spaces so that all end up right aligned
  3. See the suggestion from Matt Allington in the comments below (very nice)

Right aligning text or percentage figure in new column

For both options the preparations are the same:

Read more

Bulk-extracting Power Query M-code from multiple pbix files in Power BI

If you want to audit or analyse the M-code of multiple Power BI pbix-files at once, you start with either:

  1. a from-folder query where you filter all files of interest or
  2. a table with the full file-path-specification of the files to be analysed in “Column1”.

Then you add a column where you call the function that extracts the M-code:

Function to extract the M-code

This code is a variation of Igors function which retrieves the code from an opened pbix-file. So now you can apply it to closed pbix-files as well.

For method 1 you call it like so (as it takes the full string for the file-path as its parameter):

Read more

Create a function library in Power BI using M-Extensions

Having the ability to use own M-function the same way than native functions in Power BI and Excel has been one of my biggest wishes for quite some time. So I was more than amazed to see Frank Tonsen’s comment showing a way to do exactly this in PowerBI, that has been available for almost half a year now: M-Extensions as part of the custom connectors.

Unlike custom connectors who show up in the import-dialogue and provide a custom tailored option for importing data or creating queries, M-Extensions don’t show up explicitly anywhere in Power BI: They just do their M(agic) job to make the functions that you’ve defined in them accessible, as if they were inbuilt native functions: Type their name into the formula bar like this (1):

And enjoy the function description (2: if you’ve specified it in the definition, which is optional):

Simplest example

  1. Your functions: Number.Double and Number.Triple
  2. Combined with the keyword “shared” and separated by “;”
  3. Prefix by “section MyLibrary” gives this text:
section MyLibrary;
shared Number.Double = (Number as number) =>
2 * Number;
shared Number.Triple = (Number as number) =>
3 * Number;

 

How to make M-Extensions work

Read more

Guest Post: Recursion in M for beginners

Intro from Imke: “I’m very proud to announce the 1st guest post on my blog written by Daniil Maslyuk. His Twitter slogan: “I am a fan of Power BI and avocados” made it instantly clear to me that this young man has an excellent eye for the essence: Just what it takes to write good blog posts. So I asked him to write a guest post on my blog. As it turned out, he was just about to start his own (XXLBI.com) which many of my readers will know already: It contains some very elegant DAX off the beaten track and is a real pleasure to read. So I’m very happy that he agreed to publish with me as well:”

When I was a school student, a teacher asked me if I knew what my life path number was.

  • What’s life path number?
  • It’s the number you get when you sum the digits of your birth date again and again until you get a single-digit number

Example: if you were born on 25 December 1963, your life path number would be 2:

  1.       2 + 5 + 1 + 2 + 1 + 9 + 6 + 3 = 29
  2.       2 + 9 = 11
  3.       1 + 1 = 2

Now, I’m not a big fan of numerology, but I am a big fan of Power Query. What does the former have to do with the latter? Life path numbers are calculated recursively, and you can totally do it in Power Query! In this article, I am going to introduce you to recursion in M.

The easy (but not the good) way

Read more

Custom Connector to import Google Sheets with OAuth2 authentication in PowerBI

Recently I came across the need to connect to Google Sheets with a secure authentication process quite often, so I will share with you how and to what extend I got the custom connector working that I found here. It uses OAuth2 authentication, so you can share your workbook with selected colleagues and they will be prompted to enter their credentials in Power BI if they try to access these files.

Edit: As it turns out, the credentials work for all Google accounts. So you can download my .mez  and simply paste it into your Custom Connectors-path without touching Visual studio ( create path: [My Documents]\Microsoft Power BI Desktop\Custom Connectors ). I believe this will work for the 1st 100 users and then you have to create you own. But if you want to use it in production, I’d strongly recommend to create your own anyway (otherwise continue with section “Use Google Sheets Data Connector in Power BI Desktop”) :

Setup Google API

Go to the Google Developer API and if you don’t have a project yet, just create one:

Go to “Credentials” -> Create credentials and choose “OAuth client ID”:

Choose “Web application”, adjust the “Name” if you like and paste the redirect-url into “Authorized redirect URLs”: https://preview.powerbi.com/views/oauthredirect.html

This will return the client ID and secret for your connector:

 

Adjust the connector in Visual Studio

Read more