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 😉

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

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

Comfort Functions for Easy Profit & Loss statements in Power BI and Excel – Part2

Here comes some long awaited comfort functions for part 2 of my easy P&L series. In the first section I’ve presented the general principle on how to work with a structure using an accounts-group-table. Today I will present 2 alternatives to define the reports without specifying single accounts. So if a new accounts are added to the chart of accounts, you don’t have to adjust your report definitions: Just make sure that to fill in all the fields in your account-group-table and you’re ready to go 🙂

No need to specify single accounts

So you only need to adjust your report definitions if you add new group items. If that’s still too much, take the 2nd solution, which will even eliminate that requirement:

  1. Individual Account Layout: Just define each subtotal and determine for which subtotals single accounts shall be shown

No more specification of individual accounts

How to use it:

How to use Individual Report Layout

2. Ultrashort Account Layout: Further simplification of just defining the groups (hierarchy) that shall be shown (with option to filter on one of them)

No need to define individual group items

How to use it:

How to use Ultrashort Layout

So these 2 different layouts will both produce the same reports incl. all accounts – just like in the first example. So you can choose which layout-style suits you best – actually, you can use all 3 in parallel. You just have to make sure to grab your pivot-rows from the correct tables and in Excel to grab the matching measures, as they all have their own bridge-tables (which need to be used in the measures):

 

How it works

Read more

Dynamic & bulk type transformation in Power Query, Power BI and M

This is just a quick code-share-of-the-day of different scenarios for dynamic type transformation of multiple columns at once.

The syntax to transform the format of 2 columns (“Column1” and “Column2”) in a table (“Source”) looks like this:

Now if you would like to do one of the following:

1. Force all columns of the table to be transformed to one type (text)

The second argument of Table.TransformColumnTypes is a list of lists, whose elements contain 2 arguments: The name of the column to transform and the type to be applied.

For this dynamic approach we start with a list of the table’s column names (Table.ColumnNames) and transfer it magically to a list of list, using List.Transform with an expression with curly brackets again like this: each {_, type text}: This operation iterates through every element of the list (List.Transform) and performs the actions that follow the “each” on every element of the list (which is represented by the underscore: _)

2. Transform all newly added columns of a table to one specific type

Imagine you have a table with different column types where users can add new columns with random names. You want these columns automatically to be converted to text:

Same procedure as the first, just that you need to identify the newly added column names. Therefore you use List.Intersect with the two tables to compare in list-format (curly brackets) as shown in line 3 above.

3. Transform all columns whose name are in a list to one specific type

Let’s close with the easiest case, which you’d probably be able to find out by yourself: Say your query returns a dynamic list somewhere with column names who then shall all be converted to a specific type:

You can directly reference the List as the first argument of the List.Transform-command.

You can download the file here: ChgTypeOfColumns.xlsx

Enjoy & stay queryious 🙂

 

How to tame Case Sensitivity in Power Query and PowerBI

Have you heard of “Comparer.OrdinalIgnoreCase”?

If the answer is “No” (or something similar), you will be very delighted to hear that this is your key to disable case sensitivity in comparing operations.

So if you want to check if a substring like “car” is contained somewhere, you can write:

Text.Contains(MyText, "car", Comparer.OrdinalIgnoreCase)

and the expression will return true for all cases of car like: CAR, Car, caR… .

The official documentation looks like this:

Read more

Easy Profit and Loss and other (account) scheme reports in Power BI and Power Pivot using DAX

This is about an easy way to create typical finance reports like Profit and Loss using DAX that (unlike all other solutions I’ve come across so far) can be handled with very basic knowledge of this language like this:

Image1

The trick

The trick that makes my solution so easy lies in the fact that it requires no aggregation functions of the output-mediums like:

  • pivot-tables: who struggle with asymetric logic and are not available in Power BI so far
  • cubefunctions: who are not available in PowerBI so far

So we have to build the details as well as all aggregations into the solution as it is and don’t rely on/use any aggregation functions (This means for Excel: We have to turn off subtotals as well as totals in our pivot tables. It means for Power BI: Hurray! Finally a solution where the lack of pivot-tables doesn’t matter).

How to

The aim is to create a table/matrix with (account) details and aggregations into the rows and different slices of time-Intervalls or comparisons into the column sections. As for the columns, this will be covered by measures like [Actuals], [Budget], [PreviousPeriods], [Differences in all shapes…]. And – as the values in the columns should be the same – I’d prefer to use only one measure per column – that is fully sliceable and works on all (sub-) totals of course. … Ok – so some dreams later I found it:

MyMagicMeasure := CALCULATE([StandardMeasure], AccountsAllocation)

So you just wrap simple measures like Act=SUM(Fact[Amount]), Plan=SUM(Plan[Amount]), DiffPlan_Act=[Plan]-[Act] … into the CALCULATE together with the bridge-table as the filter-argument:

This is the many2many-technique in it’s simplest form (PostFromMarcoRusso). It all goes via simple aggregation on all accounts found in the filter context:

Image4

Our  bridge-table “AccountsAllocation” consists of one account number per simple account and has multiple rows for the (sub-)totals – being all accounts that belong to them:

Image5

The ConsKey stands for the row in our report (1) and the AccountKey_ holds the account numbers that are going to be aggregated (many (for the sub-totals) and 1 for the account-rows). So all we need is this unique and simple aggregation on AccountKey for every row in the report – with a filter from the Reports-table via our bridge table to the DimAccounts, who then filters our FactTables: 1 -> many -> 1 -> many.

Read more