This is the last part of my little series about bill-of-materials (BOM) calculations made easy with M in Power BI and Power Query: The implosion that shows where each part is used:
BOM: “Where used?”
BOM Where used analysis
When we look closely at the table “BOMReport” from the previous post, we see that the data is already there: “PathExplode” shows that the component “HL Spindle/Axis” (1) is used in the TopParentProduct “Road 150 Red, 62” (2) via some intermediate products (3):
BOMReport holds that information already
And if we filter on the example from the pivot-table from above, we can spot that pattern as well:
All we have to do is to create one row for each “Where-used” item. But as we have columns for the compenent itself already as well as for the TopParentProduct, we actually only want to create additional rows for the intermediate products.
Therefore we could split the column “PathExplode” up into its components. But that’s not necessary because it has been generated from the lists in column “PathItem”. So we just have to eliminate the first and the last item from those lists and expand them:
M-Code to extract the intermediate parts:
M-code to extract and expand the intermediate products
M-Code as text:
Subscribers can download the Excel-file:
Enjoy & stay queryious 🙂
Following up on the BOM-explosion: A comment reminded me that I had missed to present the costing techniques to calculate the total costs of each (sub)-product.
Reversing the aggregation direction
What I had shown is how to “aggregate” from parent down to child-level to retrieve the total quantity of each component within a BOM (“How many of each (sub-) components do we have to order (or build) for that bike?”) (1).
Now we reverse the aggregation direction and aggregate the total (!) quantities back up to the parents (2).
And, as this doesn’t make too much sense in an economical way, the second aggregation will be their prices (3). This will give us the sum of all part-costs (“How much will the order of all the parts cost us?”). This is also very useful for planning purposes or reconciliation of prices for intermediate products with your master data.
And if your model holds sales-data as well, you can calculate the totals costs of your total sales within each period. (4)
VAR 1: Using classical hierarchies
Recently I’ve distributed some techniques for partial matches or relative joins between tables using PowerQuery or the query editor in PowerBI. They are very flexible and powerful – yet slow.
To improve performance you can check if there is a chance to “partition” your table using a Table.Group. If you have an equality expression in your statement like we had in our rolling-12-months-exercise here for example:
You can boost performance into a different dimension by grouping your table on the “Associate”-table instead like this:
If you want to solve Excels XIRR-function with M in PowerBI or PowerQuery you have to use a goal seeking algorithm. I tried it with a binary-search and the results were quite good (on my scale):
Comparison of XIRR results between native Excel function and M
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.
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:
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:
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:
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:
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.
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:
Enjoy & stay queryious 🙂
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.
Rename Columns Variations
Only replace FirstN or LastN elements from the column names:
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:
- 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