Goal seeking and XIRR in PowerBI and PowerQuery

If you want to solve Excels XIRR-function with M in PowerBI or PowerQuery you have to use a goal-seek algorithm. I tried it with a binary-search and the results were quite good (on my scale):

Wondering if there are other solutions out there or different techniques regarding the “helper”-elements I had to include here, so please come forward 🙂

Goal-seek for XIRR

Code:  GoalSeekXIRR.txt

The goal-value is formulated in a way that it should be zero, as this is what the binary-search procedure is aiming at. In this case it’s the XNPV. Other cases could be Break-Even for example, where the accumulated sales match the accumulated costs. In that case you would write: Result = sales – costs .

Subscribers can check it out in this file: GoalSeekForXIRR.xlsx

Enjoy & stay queryious 🙂

Performance difference between Excel and PowerBI with M is huge!

I knew that the performance of M in the query editor of PowerBI was much better than in Excel, but only today I discovered the incredible difference we actually have here:

If you want to apply the BOM-solution I’ve posted here, you’ll soon discover that the performance in Excel starts to suck with large datasets. Performance decreases exponentially and my sample datasets with 4 levels and 100k rows didn’t went through, 16 GB RAM constantly at the limit, unable to do any other task at the same time.

In contrast, performance in PowerBI totally blew me away: Memory management is different. Rise in RAM-consumption was always below 3 GB, even with my largest dataset (a 5-level 1Mio (!) rows BOM table that exploded to 3,8 Mio rows). Also no sweat in CPU, so I was able to easily perform other tasks at the same time on my laptop.

The 100k rows where Excel failed, went through in a good minute, 300k rows BOM exploded in 4 minutes to 1 Mio rows: For a recursive operation (using List.Generate instead of “real recursion”), this is very acceptable in my eyes. 1 Mio rows took a bit under 20 minutes to explode to 3,8 Mio rows.

I must say at I’m really impressed with the performance of PowerBI Desktop with M for a task like this on a PC!

Anyway – if you want to work with the results of your query in Excel (which isn’t so unusual for this kind of data), you have to rely on R for exporting it to csv or SQL-server or use some hacks:

  1. Access the full datamodel via Pivots
  2. Import the table via PowerQuery  (See in the comment)

Needless to say how much I hope that we wouldn’t need these workarounds. If you agree and want to take some action, please vote for a performance improvement of Power Query in Excel.

Enjoy & stay queryious 😉

How to edit M-function documentation metadata

After the great announcement yesterday that we will be able to ship custom functions within the shared environment, I’m expecting a lot of people starting to write awesome custom functions for M. Hopefully they will all have nice function descriptions/metadata shipped with them, that makes it as easy as possible for users to apply them correctly:

Please read here how to do that.

What’s also cool: You can add your own records, like Author, Source or Link… . Only thing I haven’t found out yet: If/how to make these fields appear in the dialogue when called. Anyone an idea here?

= let
  func = () as number => 123,
  documentation = [
   Documentation.Name = “MyFunction”,
   Documentation.Description = “Returns a shiny new number.”,
   Documentation.LongDescription = “Returns a magical, shiny, brand-new number.”,
   Documentation.WhoAskedTheRightQuestion = “www.TheBIccountant.com”,
   Documentation.Category = “Number”,
   Documentation.Examples = {[Description = “The first example.”, Code = “MyFunction()”, Result = “123”]}
  ]
 in
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

If you copy this into the advanced editor, you’ll see that not only my new field “WhoAskedTheRightQuestion” but also the Description doesn’t show as well. I think it would be very helpful, if that could be adjusted.

Edit 2017-May-18: You’ll find the new documentation about this feature here. Some nice additional features like allowed values and sample values for function parameters. But no possibility to include own fields in the display.

Enjoy & stay queryious 🙂

Bill of Materials (BOM) solution in Excel and PowerBI

Handling multilevel bill of materials (BOM) without VBA in Excel and PowerBI is now a piece of cake: Just pass 4 parameters into the M-function below and it will return a table that holds everything you need for:

  1. Explosion

  2. Order list (“total quantities”)

3. Implosion (“where used”): Will be covered in next blogpost

The format of the input-data for this function needs to be like the example used from the Adventure Works 2008-database, where all products on the top of the hierarchy also have an entry in the child-column (the components), leaving the parent column blank:

BOM-Function

BOM-code beautified with Lars Schreiber’s M-editor: goo.gl/KW4p8Q

txt-file for download: BOM_Code.txt

The query “Invoked Function” invokes the function and needs the following parameters adjusted to your BOM-table:

  1. “Table”: Name of the query that holds your BOM-table
  2. “Parent”: Name of the column that holds the (parent) product ID or name
  3. “Child”: Name of the column that holds the (child) component ID or name
  4. “Qty”: Name of the column that holds the quantity per produced item

This query will be loaded to the datamodel and there I’ve added some DAX-PATH-columns that might come in handy for some cases.

New to M?

Watch this video where I show how to use the function code with your own data:

Details for techies and M-code-fans:

This technique is MUCH faster than the PC-solution I’ve posted here! (…just don’t ask me why & be prepared for significant performance drop offs once you try to modify anything…) It can also return the path for children with multiple parents, so an excellent workaround for this missing functionality of the DAX PATH-function (check datamodel in file). All other PATHx-functions will work, so just take the PATH from M. (Also the dynamic creating of multiple columns from the post above still works fine)

Noticed the clean code in step “AddFields”? M can look like a serious programming language once you strip off the elements that makes it a live programming language 😉

Subscribers can download the file with sample data and the pivots shown above:   BoM-Table4_adj.xlsx

Stay tuned until next week when I will post the pattern for the BOM-implosion (“where used”)

Edit 2017-May-14: Performance of this solution in Excel can decrease rapidly with larger dataset while it runs good in Power BI Desktop. Read details and workaround here.

Enjoy & stay queryious 😉

KPIs in Easy Profit and Loss for PowerBI

Welcome to the last part of my Easy Profit & Loss series where I will cover KPIs in rows & columns:

1) KPIs in columns

Show all your figures as a percent of turnover for example: Nice & easy: Divide current figure by the total sum of turnover:

Turnover% =
ABS (
    DIVIDE (
[ActSign],
        CALCULATE (
[ActSign],
            FILTER (
                ALL ( IndividualAccountsLayout ),
IndividualAccountsLayout[Description in Report] = “Income”
            )
        )
    )
)
DAX Formatter by SQLBI

We need to leave the current row context to retrieve the turnover-value in each row, therefore the ALL.

2) KPIs in rows

Read more

Machine Learning with M in PowerBI and Excel

Very often I have thought about trying M instead of R for machine learning problems in PowerBI. Not only because I’m such a big fan of M, but also because we don’t have the R-integration in Excel (yet?).

Leila Etaati’s brilliant series of how to use R in PowerBI for KNN-prediction (nearest neighbourhood) finally kicked this off. In order to trigger some thoughts I have structured the code in a way that resembles the R-structure. So the core M-code looks like this:

KNN in M

 

Where this sits in a function that you feed with the following parameters:

Function Parameters

 

In there, 2 functions are called, like in the R-code. While the functions already exists in R and you just have to load the necessary packages, in M we don’t have these functions (yet), so I had to build them:

Normalizing the table:

Evaluating the nearest neighbour-label:

I also added some comfort-features: The k-value will be calculated automatically and you can enter a %-value for the split between training & test-data.

Key-findings:

M has all it needs to calculate the results, but the performance can be a pain. To my understanding so far, this is mainly due to the fact that it will call the sources multiple times. Unlike in SQL-server for example, the execution plan is hidden and we also don’t have stored procedures which enable us to de-activate the re-evaluation of the execution plans with every data refresh.

While I see the point in not re-inventing the wheel, there is an aspect of how many languages we are expecting the PowerBI-users to learn. Just a thought.

File to download:
zipped pbix: KNNR2-1.zip

Enjoy & stay queryious 🙂

Use Slicers for Query Parameters in PowerBI

Reading Rob Collie’s latest cool blogpost on how to retrieve slicer selections in Power BI, I couldn’t stop thinking of how awesome it would be, if we could use this technique to pass slicer selections as query parameters to the M-queries in the query editor. Not only would we have a very convenient user interface, but – what’s actually more important at the moment – we could pass multiple values as parameters to our queries, as this is not possible at all currently:

But how to fetch them? Rob’s post simply uses cross-filtering to show the values in a separate visual. In Excel we have cubefunctions where we can pass the slicer(-selection) as a parameter. Igor Cotruta, who is describing beautiful PBI-hacks on his blog here, kindly helped me out on this: “Via DMVs. Check $system.discover_sessions for the field sessions_last_command”. This worked perfectly into the following function, in which you just have to pass the name of the measure as a parameter:

Code beautified using Lars Schreiber’s Notepad++ Script: http://ssbi-blog.de/technical-topics-english/power-query-editor-using-notepad/

 

Make sure that you have used that measure on one of your visuals, as otherwise the function cannot harvest it. Also you have to first save the file and then push the refresh-button in order to trigger the correct refresh. The above function sort of “reads the current PBI file from outside”, so it will only see the saved version.

When you do the first refresh, a dialogue will pop up, where you just have to accept the default values like this:

The example in the file below fetches temperature data where every selected year will create a unique URL and the results of all those calls is consolidated into one table. But of course, this technique can also be used to pass multiple parameter values to SQL-commands or others.

A final note: The query to extract the slicer parameters from the DAX-statement is not particularly robust and you might have to adjust it, if your slicer-selection-strings contain special characters.

Download for logged-in subscribers SlicerParameter2.zip

Enjoy & stay queryious 🙂

Blending data in PowerBI like in Tableau

Today I came across a question in the PowerBI-forum if blending data was possible in Power BI like in Tableau. Although I wouldn’t necessarily recommend it, it’s definitely is a nice challenge. So the following function will interlace the rows from 2 tables like the blending-function in Tableau does. Just that we cannot use any aggregators on the attributes and are not able to use measures, as this takes place in the query-editor.

In our example we have a table with actual figures and one with budget figures:

We want to add 2 columns from the budget table to the actual table: “Amt” and “Qty” (red). Where there’s no match of budget – figures with actuals, there need to be added rows which hold only values from the budget figures (yellow):

Blended data like in Tableau

So we could do a join in full-outer-mode, but then we would need to find a way to put the date- and AccountNo-values into the existing columns of the actual figures. Instead we will identify those rows who need to go below the actuals and then do a join in left-outer mode just to add the values of the 2 new columns.

You need to feed this function the following parameters:

  1. Name of the primary table (“Actuals”)
  2. Name of the secondary table (“Budget”)
  3. Key column names of the primary table (“Date”, “Account”)
  4. Key column names of the secondary table (“Date”, “AccountNo”)
  5. Column names for the value columns (“Amt”, “Qty”)

Table.BlendRows

 

File with sample: BlendDataTableau.zip

 

Dynamically flatten Parent-Child Hierarchies in DAX and PowerBI

If you use DAX to flatten Parent-Child hierarchies you will end up with a table that has a static number of columns (like described here). If you need a dynamic solution instead, which creates just as many level-columns as there are needed for the current data, you can use DAX’s helper-tool Power Query (or Get Data in Excel) or the query-editor in PowerBI, which uses the language M.

Another advantage of this solution is that you can script the table creation in one step (only flaw: You still need to manually adjust your hierarchy though): But it saves time in creating the table, especially if you have many levels.

2 simple steps

  1. copy the following function,
  2. add a new step to your current table where you call this function, filling in the following parameters:
    • table name (which is the name of the previous step in your M-query)
    • name of the column with the child-key
    • name of the column with the parent-key
    • name of the column who’s values shall be shown in the levels (can also be child-key)

 

Call fnFlattenPCHierarchyFunction

 

And this is the code, which you can also download below:

Read more

Automatical or Bulk- Rename Columns in Power BI and Power Query

Edit 7th Feb 2017: Friendly reader Roknic pointed out in the comments below that there’s actually an existing function for it in M: Table.TransformColumnNames 🙂

So the first of my example below would actually look like this:

Table.TransformColumnNames(Source, each Text.Replace(_, " ", "_"))

But still keeping my original post here, as the transformations in them might help for other use cases:

If you want to rename all of your table’s columns with a common rule, like “replace all spaces by underscore” or just “delete all spaces”, check out this easy method:

The above formula will replace all spaces (” “) by underscores (“_”).

How does it work:

The 2nd argument in the Table.RenameColumns-formula is a list of lists, just like in Table.TransformColumnType from this article. So we apply the same technique here: List.Transform transforms a single element from a list into a list-item, whose 2nd argument will be calculated with a Text.Replace-function.

Variations:

Rename Columns Variations

Only replace FirstN or LastN elements from the column names:

Read more