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 😉

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

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

Pivot your table-relationships in Power BI and Power Pivot

While the relationships view of the datamodel provides a very good overview which tables are connected to each other, one cannot see at a glance on which field they are connected to each other.

This is where a pivot table-view of the field-connections can be really helpful:

Pivot-table-view:

Table-Fields-Connections in Pivot-View

 

Here you see the tables on the many-side in the rows and in the columns are the tables on the one-side (of course you can change that). Add some slicers if your model is very large.

Read more

Dynamic Power Query X-mas tree in Excel

Wishing my faithful readers peaceful and happy holidays.

Hope you enjoy my jumping Power Query X-mas tree: It changes his size or decoration randomly every time you press refresh: Totally auto-M-agically 😉 (No macros and no Excel formula in any cell ! )

Just 6 lines of code and 2 formatting conditions return a table with dynamic size. So if you haven’t discovered the M-agic of Power Query or Get&Transform in Excel yet, this is a good handful of code to start your discovery with!

XMasTreeV2.xlsx

Data -> Refresh All or Ctrl+Alt+F5

Happy holidays & stay queryious 🙂

 

Speed up the PowerBI and Power Query design process

When querying large databases with Power BI or Power Query, the design of the query itself sometimes causes long waiting periods because of background loading activities. In order to speed up this process, I’m adding a filter in my tables as one of the first steps, which I can toggle on or off using a central switch. The toggle is a query that will be referenced by an additional line of code:

FilterToggle = if DBFilterToggle=1 then LastQueryStep else FilteredRowStep

filtercode2

Where DBFilterToggle is a query itself with just one value: 0 if I want to have the filter active or 1 if I want to deactivate the filter and see all the data instead.

The cool thing about this method is that the following code will work seamlessly and we can use this toggle in multiple queries at the same time: Just filter your other long tables to your desire and refer them to DBFilterToggle.

This enables me to work with a very few data which will be delivered blazingly fast (due to query folding) and move on with my design very quickly or (often more importantly) during debugging where you need to move through multiple steps quickly in order to find the errors.

I’m not sure, if this is my “invention” or I have seen this before, but so far couldn’t find the source. So please post the source in the comments if you find it.

Enjoy & stay queryious 🙂