Migrate a Power Query or Power BI file to a local SSAS instance

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher:

The steps:

  1. Import the Excel file in Power BI Desktop, save and close the pbix-file
  2. Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
  3. Open that model with Visual Studio (this will actually create a download that holds the VS-file)
  4. Open that file in Visual Studio, load the data, build and change the deployment target from Azure to you local SSAS-database before deploying.

See how it goes:

Warning: There are some limitations for the M-functionalities in SSAS (see here for example: General Overview by Microsoft or Use your own SQL … by Chris Webb), so you might want to give it a thorough test before rolling out. There are missing a lot of data sources currently, like web-queries for example who will hopefully soon be added as well.

This method has been described by Soheil Bakhshi here before: http://biinsight.com/import-power-bi-desktop-model-to-ssas-tabular-2017-using-azure-analysis-services/ 

Enjoy & stay queryious 😉

GuestPost: Newbie to Newbie Learning M-Language as your first Programming Language

Foreword from Imke: “Happy to publish my 2nd guest post here: I met Rafael Knuth in the Technet-forum where it was a joy to see how quickly he was set on fire by the M-language. When he vented the idea about starting a newbie-to-newbie-series where he would share his learning experiences and perspectives as an “Excel-guy”, I was quick enough to engage him for an intro on my site. As it turns out, he is a VERY talented communicator as well, but just see for yourself”:

I’m just a regular corporate marketing guy in his late forties with no formal programming education. However, one day I woke up and decided to teach myself to code. I had no plan whatsoever, and my learning journey was anything but a carefully planned venue. It was rather accidental fumbling & stumbling, accompanied by loads of frustrations, with frequent, prolonged breaks to recover from my failed attempts to teach myself to code.

What makes learning to code so hard?

These are the main obstacles in my views:

1) Lack of time
As a professional in a corporate environment, it’s nearly impossible to put 20 hours a week aside to teach yourself a new skill from scratch, without major sacrifices in other areas of your life.

2) Your brain’s “wrong” wiring
What makes learning so hard is the amount of knowledge you have to unlearn: “Why is my program not doing what I expect?” Because you set the wrong expectations. Rewire your brain.

3) Complexity of the subject
Coding is a hard piece of candy, bluntly speaking. There is good reason why there’s such a dramatic undersupply with good developers.

4) Lack of applicability of your knowledge
So, you did that course on Python at Codecademy. How do you put your newly acquired skills at work? Unless you prove me wrong, my answer is: Not at all.

5) Unrealistic expectations
Become a Data Scientist in a 6 month bootcamp. You will find tons of offerings like that. So, basically what it says, is: “You can be smarter than all those guys who put years and years into studying programming, mathematics, acquiring PHDs – just join our course and you’ll get there in no time.” Good luck with that.

Microsoft M-Language comes to your rescue

Read more

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 😉

Web Scraping 1: Combine multiple tables from one page in Power BI and Power Query

This is a step-by-step description of how to combine multiple tables from one webpage into one table with categories as output. You can also apply this technique to combine tables from other sources as well (like from folder method for example or multiple different webpages (see in an upcoming article)).

Sometimes the page you want to scrape has multiple tables like here:

0 – Combine multiple tables into one: Input

And you want to combine them into 1 with a Category-column like so:

1 – Combine multiple tables into one: Result

Overview

I will present 2 methods here:

  1. Append-method: This is the obvious one and is fast for just a few tables.
  2. Add-Column-method: A bit more complicated but will be faster for a large number of tables and is also suitable for a dynamic number of tables.

You will also find 2 options at the end of this article:

  1. Use custom functions for multi-step table transformations
  2. Use dynamic filters to select the desired tables

 

Append method

Read more

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

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. 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