A generic SWITCH-function for the query editor in Power BI and Power Query

Although you can easily replicate the DAX SWITCH-function via list-, table- or record functions in M, I thought it would be convenient for many newbies to have a comfortable M-SWITCH-function that uses (almost) the same syntax than its DAX-equivalent:

SWITCH (
[Month],
    1“January”,
    2“February”,
    3“March”,
    4“April”,
    5“May”,
    6“June”,
    7“July”,
    8“August”,
    9“September”,
    10“October”,
    11“November”,
    12“December”,
    “Unknown month number”
)
DAX Formatter by SQLBI

The DAX-SWITCH-function will retrieve the content of its first argument (expression) ([Month]) and check it against he first parameters of the following pairs (value). If there is a match, the second parameter of the pairs (result, here: month name) is returned and if there is no match, “Unknown month number” will be returned.

How it works

The syntax for the M-function looks like so:

M.Switch(Expression as any, Values as list, Results as list, optional Else as text)

So we have 4 parameters: The Expression just like in DAX, but then the Values and Results come as separate lists. The last optional argument is just similar to DAX again.

This allows for a very convenient entry of function parameters:

1. You can quickly enter numerical ranges:

M.Switch(Month, {1..12}, {"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"}, "Unknown month number")

2. You can super-easily refer to switch-values in tables:

It has just one minor flaw: When you refer to a parameter or another query in the Expression-field, you will be default get an error first. But removing the 2 quotes will quickly fix it:

This is because I’ve set the format of this field to “any”, as the condition can actually be of any type. But the function-dialogue has no way to handle different types currently and will transform all entries to text by default in that case.

It uses a technique that I’ve used in this article already: There you can see that the results can also be functions for example.

Function code

Most of the code is documentation (row 7 onwards) or handles the missing values: Row 5+6 will return the value from the optional 4th argument (Else) if used, otherwise the default-value: “Value not found” will be returned. The main function logic (in row 4) is the positional index indicator: {List.PositionOf(Values, Expression)} that is applied to the list of Results. List.OfPositions will return the position (number) of where the Expression has been found in the list of Values. That x-th value will then be picked from the list.

Enjoy & stay queryious 😉

List.SelectPositions in Power BI and Power Query

With this new custom function “List.SelectPositions” you can easily select items from a list by just passing a list of their positions within it as the parameter.

What it does

Say you have a list with numbers {1..5} and want to select the 1st, 4th and 5th element from it. Then you can pass these positions to the function as another list: {0, 3, 4}.

ListSelectPositions({1..5}, {0, 3, 4}) will return: {1,4,5}

You see that I’ve decided to follow the zero-based counting principle here, that you find throughout M in the query editor. If you don’t like that, you can use the optional 3rd parameter to let it start to count from 1 instead:

ListSelectPositions({1..5}, {1, 4, 5}, 1) will return {1, 4, 5}

But if you have entered positions that don’t exist, the function will return an error in their positions by default:

ListSelectPositions({1..5}, {1, 4, 5}) will return {2, 5, Error}

because there is no 6th element (you’ve omitted the 3rd parameter that allows you to start counting with 1).

But you can change this behaviour as well through the last optional 4th parameter: Setting it to 0 will fill the missing positions with null like this:

ListSelectPositions({1..5}, {1, 4, 5}, null, 0) will return {2, 5, null}

and setting it to 1 will eliminate it and shorten the list like this:

ListSelectPositions({1..5}, {1, 4, 5}, null, 1) will return {2, 5}

These additional error-handling-options of the 4th parameters are useful for dealing with badly formatted data and if you want to learn more about it, just let me know in the comments so that I can prioritize it.

Function code

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. The delimiter is an optional argument and by default set to space ” “. So you can leave it blank if that’s fine for you or enter a different value (like “,” for a comma) if needed.

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.

Edit 28-Jan-2018: While searching the web to see if one of my next blogpost-topics have already been published somewhere else already, I came across Ivan Bond’s blogpost who used this same technique over 2 years ago here: https://bondarenkoivan.wordpress.com/2015/10/11/transform-table-column-using-own-function-in-power-query/ . It’s a very good read and you will also learn how to use a function like this to transform an existing column instead of adding a new one to perform the operation like in my example above, so don’t miss it.

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

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

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

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