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 🙂

Happy Spreadsheet Day! (Or how to escape Excel-hell)

When reading horror-stories about Excel-hell describing how dangerous it is to use Excel in corporate environments, I cannot help but to think of this hilarious video describing the fatal consequences of acting without common sense: Just don’t do stupid things with it.

Although Excel comes nearly for free (in relation to what value it delivers) this doesn’t mean that you don’t need to invest in applying proper techniques (like in any other profession). There’s training and best practices for every different need.

But the best thing about Excel seems largely unknown still: Since the invention of Power Query it has never been easier to be save around Excel than before: A magic tool that can solve many of the problems that cause Excel hell: Repetitive tasks: The little adjustments and extensions that pile up when you use your workbook again and again and are often performed without realizing the (meanwhile complex) context of all the standard-Excel-elements involved: Power Query will prevent this mess. It will help you organize and automize your repetitive tasks in Excel.

Read more

Perfect Analysis Services (SSAS) reports in Excel using Power Query

Excel-reports on SSAS cubes (multidimensional and tabular) can have some flaws that now can be overcome by using Power Query for sourcing your cubedata:

  • filter your cube by complete Excel-tables without loading them to the model/cube
  • Apply nice number and date formats to non-measure number and date fields in your row- or column section
  • create fast detailed reports (multiple attributes in your row sections, overcome the slow MDX that the pivots on cubes produce)

As with the recent Power Query update (26) you can now create your own MDX and DAX-statements for retrieving data from a cube, it is also possible to pass individual parameters from your Excel-sheet to the queries. This is a prerequisite for dynamically reducing the number of returned fields to the query, thereby allowing a decent performance of these reports.

So how about filtering the query by a table that sits in your local Excel file? Can we do an inner-join just like on the SQL-server-sourceRead more