PowerQuery

Non-linear Break-Even Analysis in PowerBI

by

A break-even analysis tells you at which value of the parameter in question your profit-calculation will turn positive (link). Here we need to sell at least 173 at a given price of 20 before we’ve recovered all our costs:

If your variable costs are constant, you can solve it by this formula:

BreakEvenQuantity = Total Fixed Costs / (Unit Sale Price - Unit Variable Costs)

You’ll find tons of examples on how to do this in Excel like here .

Non-linear cost structure

But in real life, the variable costs often depend on certain quantities as you get discounts for purchasing large amounts. The following table shows a cost structure with fix costs in row 1. The 3rd column “FixOrQty” indicates if the cost item is fix or dependent on the quantity (Qty). The 2nd row contains a variable cost that is constant with 2 for all quantities. Row 3&4 show a variable cost of 8 for quantities up to 100 and if you purchase more than 100 the costs will be lowered to 2 for all additional quantities. Row 5-7 have a similar structure, but with 3 quantity ranges:

 Solve with goal-seek algorithm

Read more

Import multiple files from Dropbox folder into PowerBI and Excel (via PowerQuery) at once

by

Edit 2nd October 2021: Unfortunately this method doesn’t work any more, as Dropbox now requires proper OAuth authentication.

Below you’ll find a video where you can see how easy it is to import multiples files from a Dropbox folder into PowerBI or Excel at once.

There are 2 different methods to grant access to your Dropbox: Grant access to the whole Dropbox or to a (newly created ) folder only. I will present the folder-method, as granting access to your whole Dropbox is really dangerous in my eyes – unless you are prepared to share it all publicly: The token generated will allow anyone to read your data. So also all those people who you’ve sent this beautiful dashboard where you just forgot that it contained your token…

To make it super-easy for you, I’ve created a function that you can download here: fnDropboxFolder

The code for it I’ve got from this thread in the PowerBI forum, which contains some additional useful information and a link to a solution with a custom connector for PowerBI, making it easy to deploy in a corporate environment (designed by Igor Cotruta).

Just watch how it works:

Some screenshots to follow along:

Read more

Unpivot by number of columns and rows in PowerBI and PowerQuery in Excel

by

Today I’m sharing with you one of my killer M(inja)-strikes: Unpivot a table by simply passing the number of columns that shall remain (at the left side of the table) and the number of rows (who hold the header-information) as parameters to them.

This is not only incredibly flexible (multiple header rows), but also very robust: You don’t have to care about changing column names for future refreshed or when you apply it to partitioned tables where the partitions itself have different column names already.

Unpivot by numbers

here: 3,2 (number of columns, number of rows):

returns:

Flexible and robust:

Your table has to be prepared as follows:

  • The columns that shall NOT be unpivoted must stand on the left side of your table. Their number must match the 2nd parameter you feed into the function (“FirstNColumnsToKeep”)
  • The header rows that shall be unpivoted must sit in the first rows of your table. So one of them still sit as the header row itself, you have to demote it (Home -> Transform -> dropdown at “Use First Row as Headers” -> “Use Headers as first rows”)

M-Code

Read more

Performance Tip: Partition your tables at crossjoins where possible – PowerQuery PowerBI

by

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:

Read more

Performance difference between Excel and PowerBI with M is huge!

by

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.

Read more