The BIccountant

RegEx in Power BI and Power Query in Excel with Java Script

From time to time Huang Caiguang sends me some geeky M-code that turns out to be very useful (like this for example). Today it’s utilizing java script code for RegEx (regular expressions): The Web.Page function can execute JS code and you can pass strings from you M-environment to it with simple string concatenation. So escape the js-code and use the ampersand (“&”) to reference the string. As a function it looks like so:

let

fx=(x,y)=>Web.Page(

“<script>

var x=”&x&”;var y=new RegExp(‘”&y&”‘,’g’);var b=x.match(y);document.write(b);

</script>”)[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in fx(“””hello012中国1235″””,”\\d+”)

This code extracts all decimals (“\\d+”) from the string provided as the first argument and concatenates them with a comma. But just try for yourself by pasting the code into the advanced editor.

This doesn’t work in PowerBI service, so if you need it there, you can switch to an R-script that can be used with a personal gateway. But for Excel users, this is the way to go.

Please vote for a native implementation in M here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8892295-regular-expressions-support-in-power-query

Enjoy and stay queryious 😉

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:

Import the Excel file in Power BI Desktop, save and close the pbix-file
Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
Open that model with Visual Studio (this will actually create a download that holds the VS-file)
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

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

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:

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:

Append-method: This is the obvious one and is fast for just a few tables.
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:

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

Append method

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.

New Generation Finance – Accounting – Controlling using Microsoft BI stack