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):

MQueriesPBIX([Folder Path]&[Name])

And for method 2 like so:

MQueriesPBIX([Column1])

This returns a table with one row per code-line.

Function to identify query- & stepnames

The following function processes this further and adds columns with the query- & step-names for further analysis:

You call it within an added column again, with the name of the previously created column containing the code (“Code”) like this:

MetaQueriesPBIX([Code])

This would be much easier, if we had a proper API like requested here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7345565-power-bi-designer-api 

That API would also enable us to bulk-retrieve other useful information from the file like everything about the DAX data model like it is currently possible with .bim-files from tabular models hosted on SSAS (blogpost with details to follow). So please vote for that feature if you find it useful as well!!

Enjoy & stay queryious 🙂

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

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”:

Format as Hyperlinks

And make sure that future refreshes are automatically formatted as well:

Make sure format sticks

This of course works with all other excel-formulas as well. So if you’re still struggling a bit to calculate your numbers in Power Query, you can simply create a column that holds your Excel-formula which you “activate” by the replacement 🙂

The formula

This formula creates the new column, bringing in the content of columns “URL”  and “Friendly Name” to the Excel-formula:

Table.AddColumn(Source, “ExcelLink”, each “‘=HYPERLINK(“”” & [URL] & “””, “”” & [Friendly Name] & “””) “)

Quotation marks and escape signs

You’ll probably notice the multitude of quotation marks: Quotation marks are used in M to indicate that a text-element is following and will also be used at the end of a text-element. In that context, they are called “escape signs”. So what happens when your text ends with a quotation mark like here: ‘=HYPERLINK(” ? How to prevent the ending quotation mark to be mistaken as escape signs and returning: ‘=(HYPERLINK ( , so cut off the last quotation mark? You add 2 additional quotation marks: That will keep one of it in the text-string.

Subscribers  can download the file here: ClickableHyperlinks.xlsx

Enjoy & stay queryious 🙂

How to open a complex JSON record in Power BI and Power Query

Today I’ll show you a very useful technique how to deal with a JSON record that contains a wild mixture of different elements like this:

If you click on one of the expandable elements, their content will be shown, but you’ll loose all the “surrounding” information (metadata) that is visible now. This is often an issue, regardless if you want to create multiple tables from it to build a star-schema or just need a handful of fields or a denormalized table. But with a little help from M, you’re good to go:

Table.FromRecords( { MyJsonRecord } )

Will returns this:

With this move, every expansion of one of the expandable elements will keep the existing data in place:

Create one big flat table

Simply expand one element after each other to create a denormalized table

Create star schema

For multiple tables, keep this query and reference it to create you (sub-)tables. Always keep the Id-column as the key (!) to combine all the tables in your data model later. (Provided you use this in a function for multiple entities/series)

Best is to play with it, so just past this code into the advanced editor:

 

If your JSON-record has a different structure with “just” header and data in different fields, this technique will be more suitable for you: http://www.thebiccountant.com/2016/04/23/universal-json-opener-for-quandl/

Enjoy & stay queryious 🙂

How to create and use an R-function-library in Power BI

Edit 10-10-2017: There is also a (simpler) way to run a custom function library described here: http://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/ . If you go that route, the only point of interest in the article might be how to create your function library automatically.

Once you’ve discovered the huge potential R gives you to expand your analytical toolbox in Power BI (check some tips & tricks in my previous blogpost if you haven’t already), you might wish to have all your awesome functions conveniently at hand when designing new solutions. And thanks to M, there’s actually nothing easier than that: R-function-library in a record (which works just the same for M-functions 🙂 )

Put your functions into a record (fnr) with the function name as the field name and the function itself as the value: One query to hold them all (and not cluttering your editor pane) and ready to use as if they were native functions:

R-function-library

Use

will export content of my query “Actuals” to csv-file on my desktop.

  1. fnr is the name of the record. You can give it your own name of course, I prefer to keep this as short as possible.
  2. followed in square bracket is the name of the function (record field name)
  3. in ordinary brackets you have the function arguments just like in standard M (record value)

Create record

Read more

Tips and Tricks for R scripts in the query editor in Power BI

Especially if you are new to R, there are some things one needs to know to successfully run R-scripts in the query editor of Power BI. I will share them here along with some tricks that made my R-life in Power BI easier:

How to get started – useful links:

Input:

You can feed multiple tables into the R-script

If you click the icon “R script”, the table from the previous step will automatically be passed as the “dataset” to the R-script. So if you don’t fill in any R-code, this will happen:

image

But if you need the content from other tables as well, you just add them into the square brackets like this:

image

“Documentation” looks like this:

image

You can use parameters in the R-script

Apart from tables, you can also use text strings as parameters in the script. They need to be inserted into the code with preceeding “& and trailing &”:

RExportCsv= R.Execute(“write.csv(dataset,” “&CsvExportPath&” “)”,[dataset=Actuals])

Beware that they must be text. So if you want to pass a number, wrap it into Text.From(…).

You cannot use anything else apart from tables and parameters in the R-script

Well, at least I haven’t managed it Smile

R-life gets easy-peasy if you use M-functions for your R-script

Read more

How to import from Excel with cell coordinates in Power Query and Power BI

There might be occasions where you want to import data from Excel into Power Query or Power BI using cell coordinates like a range from E3 until G9 for example (“A1 cell reference style”). The function I provide below also caters for the potential pitfalls of this task that Maxim Zelensky has described in his article.

Background

If your worksheet has one leading empty row and column, the import will ignore them and automatically return the range starting from B2. So to fetch the range E3:G9 you have to delete the first row and the first 3 columns. But as Maxim has found out, remaining formats on empty cells will lead to an import of empty rows and columns. So the number of rows and columns to delete will vary and is hard/impossible to predict.

Method

The range that PowerQuery or PowerBI will import is stored in the Excel-file already in the sheet-data and the xml looks like this (“Sample3” from Maxims data):

The imported range is E1 till J12, as the first rows contain formatting instructions, and will therefore be imported as well. In the 3rd row E3 shows up with the first value, which is surrounded by “<v>”.

This is how it looks like in the Xml.Table in the query editor:

Task is to calculate the number of rows and columns delete, considering the individual offset that is caused by the formatted empty cells.

Code

So I’ve cooked together these ingredients in a pretty massive code that you can download here: fnImportFromExcelCellCoordinates.txt

How to use the function

Read more