M

Table.TransformColumns – alternative in PowerBI and PowerQuery in Excel

by

This article shows a trick for a little problem that annoyed me for quite some time: How to get Table.TransformColumns transforming the values of a column with a reference to a value (from the same row) of another column?

1 Replace text by a value from another column

So instead of adding a new column where the “*” is replaced by the value from column “WhildcardValue”, I just want to perform the replacement-operation in the original “Text”-column, so that I don’t have to rename and delete the other columns later:

So far, I always ended up fighting with Table.TransformColumns-function and got quite frustrated because I couldn’t find a way to reference the (row-) value of the other column. As it turns out, I was fighting the wrong target here, because Table.ReplaceValue is actually the saviour for this challenge:

1.1 Check column “Text” -> Transform -> Replace Values

So in (4) we just filled in a dummy-value, which we’re going to replace with a reference to the desired column: “each [WildcardValue]” like this:

Read more

SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel

by

Edit: There is currently no interest at Microsoft to change the current behaviour: http://community.powerbi.com/t5/Issues/Bug-Query-folding-not-working-with-non-SQL-datasource/idi-p/225100#M11611 . I found that this workaround works also pretty well (faster in most cases), as long as your filter-tables are not too long: http://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503 Are your SQL queries that filter with a non-SQL-table slow in PowerBI and PowerQuery in Excel? Then … Read more

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