Unravel cumulative totals to their initial elements in Power BI and Power Query

Recently I came across an interesting request where someone wanted to un-cumulate their quarterly YTD-figures (green) into their single quarters values (red) like so (“Unravel cumulative totals”):

Task

Retrieve every Quarters Amount from the Quarter To Date values (“YAmount”)

Method

To retrieve this value, one would have to start with the first value in the year. This is also the value of the first quarter, but for the 2nd quarter, one would have to deduct the value of the first quarter from the cumulative value of the 2nd quarter. So basically retrieving the previous cumulative row and deduct it from the current cumulative row. Do this for every row, unless it’s the start of the year or belongs to a different account code in this example:

Grab previous cumulative values, but only within the valid ranges

(Although for the data given in the sample, it would be sufficient to just take the year as a discriminator, but to be on the save side, I would suggest to include the different accounts as well)

Solution

Fortunately I’ve already written a function to grab the previous rows with lots of bells and whistles, that also includes the option to include grouping parameters. So if you copy the function code to the advanced editor of an empty query and name this “fnGetPreviousRow”, you just have to add a new step with the following code:

fnGetPreviousRow(#"Changed Type", null, {"YAmount"}, {"Account code", "Year"}, null, null)

Add a step to call this function (don’t go via “Add a column” here !!)

Call function (Previous stepname: red, Amount column: yellow, Grouping columns: green)

 

This will retrieve the previous row from the cumulative “YAmount” within every combination of “Account code” & “Year” and fill in nulls in the respective first rows. So when you then add another column that subtracts the new Value from the CumTotal, you will retrieve nulls for the first rows. This is not the desired outcome and I suggest to go back to the previous step -> check the “YAmount.Prev”-column and replace “null” by “0”. After that the calculation returns the correct result:

Result with single quarterly values (“Unravel cumulative totals”)

File to download

You can download the file to follow the steps:  Unravel cumulative or running totals

Enjoy & stay qeryious 😉

Comparing Table.AlternateRows with List.Alternate in Power BI and Power Query

I must admit that I had more than one unsuccessful attempt to try to fully understand how the List.Alternate-function works. What helped me at the end, was the function Table.AlternateRows. It pretends to be similar to List.Alternate, but holds some surprises that I will uncover in this blogpost:

How Table.Alternate works

Say I have the table below and want to retrieve just the letters that appear in every 2nd row:

Table.Alternate – Remove every other row

I find the dialogue that appears very helpful and intuitive:

It clearly is a removal operation and here I want to remove the 1st row from my table (“1”), and just one at a time. Also want to keep just one row (“A”) before the next one is removed (“2”)  and so on.

In the formula bar, this step will be translated into this M-code:

Table.AlternateRows(Source,0,1,1)

If you would have expected it to be translated to: Table.AlternateRows(Source,1,1,1) instead, you might have forgotten that the M-language in Power Query starts to count at 0, so the first row to remove is expressed by the 0 here.

List.Alternate should work similar

So if my input is a list instead of a table like below, I should expect a similar result than the sample above if I tweak the code a bit, shouldn’t I?

List.Alternate – produces a different result

But hey: What’s wrong here? Not a single element has been removed from the list !!

So let’s have a look into the documentation:

List.Alternate – Function Documentation

and compare it with the Table.AlternateRows documentation:

Table.AlternateRows – Function Documentation

Hm – at least we have one match here: The “offset” parameter is included in both functions. But it is the first (number) parameter in the Table-function and is at the last position in the List-function. So let’s move it around then like this:

List.Alternate – same result with different parameter order

There we are 🙂

So the order of the function parameters is different here. Also the other parameter names are different and their description. I find them much easier to understand in the Table function and of course, the function dialogue there helps to understand what shall happen as well.

Enjoy & stay queryious 😉

Performance tip for List.Generate (1): Buffer your tables in Power BI and Power Query

Lately I was working on a fairly advanced allocation algorithm on large data which forced me to search for different tricks to improve performance than those that you can find on my site here already.

Background

I was using List.Generate to check for every month in my table, if there was enough free capacity on a platform to start new wells. As every well had a certain production scheme (producing different amounts for a certain length of time), I first had to check the total production amount of active wells before I could determine the spare capacity for a new month. So I had to look into every active well, grab the capacity of the new month and add it up.

Therefore I’ve stored the active production schemes in one table in my List.Generate-record. That lead to an exponentially decreasing performance unfortunately.

Solution to improve performance of List.Generate

Buffering my tables in the “next”-function reduced the query duration by almost 70% !

Although a Table.Buffer or List.Buffer is always high on my list when it comes to performance issues, I was fairly surprised to see that behaviour here: As List.Generate returns the last element of its list as an argument for the next step, I was always assuming that this would be cached (and that was the reason because List.Generate performs recursive operations faster than the native recursion in M). Also, I had just referenced that table once ane in such a case, a buffer would normally not have come into my mind. (But desperation sometimes leads to unexpected actions …)

I also buffered a table that had just been referenced within the current record (and not recursively) and this improved performance as well. (Although in that case, the tables has been referenced multiple times within the current record). But this buffer didn’t have such a big impact on performance than the one on the table that was referenced by the recursive action.

Code

Here is some pseudo-code illustrating the general principle:

Solution with buffers:

How to improve performance of List.Generate: Use Table.Buffer

 

Is that new to you or have you made the same experience? Which grades of performance improvements did you achieve with this method? Please let me know in the comments!

Enjoy & stay queryious 😉

How to do a real VLOOKUP (false) in Power Query or Power BI

When you merge tables with distinct keys in Power Query you will get the same result than the VLOOKUP-function in Excel returns (if this is new to you, check out this article for example: https://www.myonlinetraininghub.com/excel-power-query-vlookup) .

But how to retrieve only the result of the first row, if the lookup-table has multiple rows with the same key?

 

Background

Say you have a dimension table for products:

Product table with one row per Product

 

 

 

 

and a transaction table with multiple entries per product:

Transactions table with multiple rows per Product

 

 

 

 

 

The task is to create 2 additional columns in your dimension table. One to show the first price at which the product has been sold and the other one the corresponding first date:

Select only first rows per Product

If you merge the transactions to the dimension table and expand it, you will end up with as much rows in the dimension table as there are in transaction table.

Problem

So how to retrieve only the elements of the first row of the matching tables? I’ll show you 2 different methods:

Solution 1 – Tweak the aggregation code

This is very quick to implement if you just want to return one or a few columns from the lookup-table: In the dialogue where you usually expand the columns, check “Aggregate” instead and click on one of the suggested aggregations for each column that I’m interested in (I simply ignore for a moment that these are not the aggregations that I actually need):

Choose one (false) aggregate per column

 

 

 

 

 

 

Now I tweak the code in the formula bar like so:

Tweaking Code for real VLOOKUP

Replacing the default aggregations by what I need (in red: List.First) and adjusting the column names directly in that command (in green: just to save one manual step later).

To avoid long query durations on large tables, you can transform the key column of the dimension table to a real key column, like Chris Webb has described here: https://blog.crossjoin.co.uk/2018/03/16/improving-the-performance-of-aggregation-after-a-merge-in-power-bi-and-excel-power-query-gettransform/

Solution 2 – Add a column that selects the whole desired row

If you want to retrieve many more columns from your lookup table, the method above can become a bit tedious. Then it might be easier to add a column, that grabs the whole first row instead: Table.First would do that job:

Add a column to retrieve the full first (or last) row

Then simply expand out all fields that you need.

Bonus

You can use many different selection operations with this technique: So List.Last or Table.Last would give you the latest prices for example. This would actually be a more realistic use case here … and is the reason why I didn’t solve the original problem with just removing duplicates 😉 .

Enjoy and stay queryious 😉

Improve import of Excel sheets with empty rows and columns in Power Query and Power BI

When you import Excel sheets who have empty leading or trailing columns and rows (showing null-values), you can substantially improve the complexity and speed of your import process with a simple trick:

Remove the reasons for the empty trailing rows and columns 😉

Background

Usually, when you import data from an Excel sheet, Power Query will automatically detect the used range in a sheet and will just return those rows and columns who have content in it. So how can it come that in some cases, additional rows or columns are returned who have nothing but empty values in them?

Reason

The reason for it can be cell formatting of empty cells. They often occur in old workbooks where cells have been deleted. These cells will be returned with a null-value during the import process with Power Query. See this blogpost for more details of potential pitfalls that come with it.

Solution

The “Inquire” Excel Add-On lets you clean any excess cell formatting. After you’ve executed this command, Power Query will not import any of those leading or trailing empty rows or columns any more. Often this will reduce the file size of the Excel files dramatically as well.

Effects

You will benefit from:

  • simpler query logic
  • potentially huge improved import speed, due to the reduced file size

Enjoy and stay queryious 😉

Fast and easy way to reference previous or next rows in Power Query or Power BI

When you search the web for solutions to reference previous or next rows in the query editor of Power BI or Power Query, you will find many solutions that base on an added index-column. But the problem with these solutions on large tables is that performance will range between slow and extremely slow. In this post I’ll show a faster method with function Table.ReferenceDifferentRow .

Basic mechanism

This new mechanism doesn’t use an index that is either used to merge the table with itself or to be referenced as a new row index. Instead, I “simply” add one table next to the other. To retrieve the previous row from a table, I reference the original table, delete its last row and add a blank row on top. That will “shift” the first row to the second row. Then I “put” this table just right to the original table, without referencing a key or applying any sort of logic. This will speed up the process considerably:

Shift table to reference different rows

The key of this method is the Table.FromColumns-function: It creates a table from a list of columns in the order of the columns in the list. So I just have to find a way to turn 2 tables into 1 list of columns:

Table.ToColumns(OriginalTable) & Table.ToColumns(ShiftedTable)

will do this job. Table.ToColumns will turn a table into a list of columns and the ampersand (“&”) will concatenate the lists from both tables.

The function

I’ve included this basic mechanism into a handy function with some bells and whistles: “Table.ReferenceDifferentRow”

 

How it works

  1. The only mandatory parameter is your table and then it will return a table with the previous rows values of all columns. So Table.ReferenceDifferentRow(MyTable) will return the result from above.
  2. The default-value for this parameter is set to -1 to return the previous row if you omit it. If you want the values from the next row instead, fill in 1. 2 will return the overnext and -2 the pre-previous row. This is what Table.ReferenceDifferentRow(MyTable, -2) returns:

    -2 will return the pre-previous row

     

  3. You probably just need one or a few columns/fields from the previous row: In the 3rd parameter you can enter a list of column names to be returned:   Table.ReferenceDifferentRow(MyTable, null, {"Value"}):

    Select specific columns

     

  4. Quite often the previous values shall only be returned within a group of rows. (That’s when you use [MyColumn] = EARLIER([MyColumn]) in DAX). You can enter a list of group-columns in the 4th parameter of this function: Table.ReferenceDifferentRow(MyTable, null, null, {"Product"})

    Group by columns

  5. By default, the suffix “.Prev” will be added to the new column names. Use the 5th parameter to change if needed. In this example, I reference the row below using “1” for the 2nd parameter: Table.ReferenceDifferentRow(MyTable, 1, null, null, "Next")

    Changing suffix and referencing next row

  6. If performance is still too bad, you can try to buffer the input table. Any value in the 6th parameter will do that for you (although I haven’t seen a performance improvement for my test cases).

 

Why not use DAX?

Referencing a previous row in DAX is still faster than my method above. So if you can do it in DAX, go ahead (create an index in the query editor and use LOOKUPVALUE like shown here: https://powerpivotpro.com/2015/03/how-to-compare-the-current-row-to-the-previous-row-in-power-pivot/. ) My function is for cases where you have to stay in M.

Enjoy & stay queryious 😉