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

Number.Mod rescue pack for Power BI and Power Query

If you use the M-function Number.Mod in Power BI or Power Query and expect the same result like in Excel or DAX, you are probably in good company.

But if the signs of the number and the divisor are not the same, M will differ from Excel and DAX:

Number.Mod in M is different

This is by design, so you can use this this formula instead, if you need matching results:

[Number] – [Divisor] * Number.RoundDown( [Number] / [Divisor] )

Enjoy & stay queryious 🙂

How Power Query can return clickable hyperlinks with friendly names to Excel

When you use Power Query as an Excel-automation-tool rather than just to feed the data model, you might want to return clickable hyperlinks that carry friendly names. This doesn’t work out of the box, but with a little tweak it will be fine:

The trick

Return a text-string that contains the Excel (!)-formula for hyperlinks, preceded by an apostrophe  ‘ . After the data has been loaded to the sheet, check the column and replace ‘= by = to activate your Excel-formula:

Activate the HYPERLINK formula by replacing ‘= with =

You can then format the column to “Hyperlink”:

Read more