New M-function: Table.TransformColumnTypesToFirstRowsTypes for PowerBI and PowerQuery

The following function automatically transforms all columns to the types that have been detected in the cells of its first row. Provided they come as: Number, date or text (but you can add additional type conversions if you need them.).

It also has some rough edges: If the first value is empty, the column will be converted to text. Also, it contains the (improved) logic from this article:  So if a date is written in a way that it could also be a number, then it will be converted as a number. To minimize the room for errors here, I’ve converted the values to text first, but this is still something to watch out for. But in very many cases it will just do what you have long been looking for:

Use cases:

  • You don’t want to use the automatic but static/hard coded type-conversion in the 2nd step (because you know you’re table is going to have more columns in the future and you want to cater for proper type-conversion of them as well)
  • You’ve lost your column types due to some other command (like Table.ReplaceValues)

M-Code

Code to download: TableColumnTypesToFirstRowsTypes.txt

 

Enjoy & stay queryious 😉

How to expand a column that cannot be expanded in Power BI and Power Query in Excel

Especially when working with JSON-data, you might end up with a column that has elements of mixed types in it. The expand column – arrows will be missing, but some elements still need to be expanded, like here:

But there is an easy way to fix it:

Transform to expandable column

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type list) then _ else {_} }} )

It transforms the “Column1” from table “Source” by checking, if the content of the each row ( _ ) is of type list and if yes, keep that value ( _ ) and if not, transform it to a list (by framing it into curly brackets {_} )

Syntax for tables

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type table) then _ else #table({“Column1”}, {{_}} ) }} )

Syntax for records

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type record) then _ else [a=_] }} )

File for Subscribers to download: HTExpandColumnThatCannotBeExpanded.zip

Enjoy & stay queryious 😉

Table.TransformColumns – alternative in PowerBI and PowerQuery in Excel

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

Non-linear Break-Even Analysis in PowerBI

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

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

Bill of Material (BOM) Explosion Part2: Costing in Excel and PowerBI

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

Read more

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.

Read more

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

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

Conditions in FirstN, LastN and other xN-functions in M, PowerBI and Power Query

Today I discovered that we can use conditions in many of the N-selecting functions where one/I would normally expect just a number-expression for the N:

Table.RemoveFirstN( table as table, optional countOrCondition as any)

So apart from being able to select a certain number of rows to be removed, we can pass a condition (as function). This condition will iteratively be checked for every row in the table (from top or bottom) and as long as every (next) step returns true, the resulting range will be removed. So as soon as one row breaks the condition, the process will stop.

I find that totally awesome, as we can now remove all top-rows who have an empty field in Column3 like this for example:

Table.RemoveFirstN(<MyTable>, each each (_[Column3] = null or _[Column3] = “”))

Yes, this will remove the first sequence of consecutive nulls in the table. So all other rows with nulls in the table coming later after a non-null value has “broken in”, will remain.

This is the list of function, where you can use this M-agic:

Enjoy & stay queryious 🙂