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 _[Column3] = null)

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 🙂

Comfort Functions for Easy Profit & Loss statements in Power BI and Excel – Part2

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:

  1. 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

Read more

Easy Profit & Loss (and other account) statements in PowerBI and Excel – Part2

Welcome to part 2 of my series of easy Profit & Loss and other account statements in Power BI and Excel. In the first part I introduced the general principle of creating asymmetric shaped reports who use just one measure per column (you should have read this article in order to understand this post here).

How the technique works

This technique capitalises the aggregation power of the Vertipaq engine and creates a bridge-table between your DimAccount-table and the ReportsAccountsLayout-table. In there for every line of your report, all accounts that belong to the (sub-)totals are matched (“AccountsAllocation”). This table can get very long, but the engine can handle this easily:

Different use case: Account-groups-tables

In the first example we’ve worked with a chart of accounts, which had a parent-chield-hierarchy defining all the subtotals of the report. In this example we’re working with a different setup, using the good old DimAccountsGroups-table. Just one row per account and the columns are coming in pairs, containing the group-criteria and the sort-order for the report:

Individual Report-Layout

We also need a second table (ReportsAccountsLayout) that holds the definitions of the report-layouts like this:

Read more

Incremental Load in Power BI using DAX UNION

This article describes the latest workaround for incremental load in PBI (thx to Taylor Clark for stressing this out!). It’s not very dynamic, as it doesn’t automatically load the difference to the existing data. Instead you have one query that contains your old data (which will be kept) and another query that grabs all data that comes after the last item from your old data. But at least it’s a technique that works without a hack:

Incremental Load Process

 

1 Create “Old Data”: “DontRefresh”

So it’s up to you to split up your long table or web-load activities and load your “old” stuff into one query (“DontRefresh”), perform your transformations and then load into the data model once. Then go back to the query-editor and disable the option “Include in Report Refresh”.

2 Create “New Data”: “Refresh”

Then take the cut-off-filter-criterium and use it to define the load of the new data that will subsequently be refreshed (“Refresh”). Transform your transformations from the first query into a function to make sure both tables have the same structure and load it to the data model (leaving the default load options to refresh).

3 Create table using UNION in DAX

In the data model, you create a new table that appends both tables to each other (and hide both input-tables from client view):

Create table in DAX using UNION

Another reason why this is not ideal is that fact that you cannot perform data transformations in the query-editor that iterate over the whole table. So I really hope that incremental load will once be a native functionality in PBI. Please vote for it here, as Microsoft prioritizes many of its activities on customer feedback: I vote for incremental load

Just picked up a useful tip from Mimoune Djouallah, to use a syntax like this:

union (summarize(table_current, field1,field2),summarize(table_history,field1,field2))

Which highlights the difference between the Append-command from M and the Union from DAX: The Union function requires the columns to have the same order in your table.

4 Why not use Append in the query-editor instead?

Another drawback of the current implementation is a somewhat unintuitive behaviour of queries which have been set to “Don’t include in Report Refresh”: As a standalone-query, they will behave as expected and not refresh. But once you reference them by a separate query or within an append-operation, they will refresh their results. So beware of this potential trap!:

Unexpected Behaviour Warning

 

Link to file: IncrementalLoadDaxUnionV2.zip

So don’t forget to vote and stay queryious 😉

Dynamic & bulk type transformation in Power Query, Power BI and M

This is just a quick code-share-of-the-day of different scenarios for dynamic type transformation of multiple columns at once.

The syntax to transform the format of 2 columns (“Column1” and “Column2”) in a table (“Source”) looks like this:

Now if you would like to do one of the following:

1. Force all columns of the table to be transformed to one type (text)

The second argument of Table.TransformColumnTypes is a list of lists, whose elements contain 2 arguments: The name of the column to transform and the type to be applied.

For this dynamic approach we start with a list of the table’s column names (Table.ColumnNames) and transfer it magically to a list of list, using List.Transform with an expression with curly brackets again like this: each {_, type text}: This operation iterates through every element of the list (List.Transform) and performs the actions that follow the “each” on every element of the list (which is represented by the underscore: _)

2. Transform all newly added columns of a table to one specific type

Imagine you have a table with different column types where users can add new columns with random names. You want these columns automatically to be converted to text:

Same procedure as the first, just that you need to identify the newly added column names. Therefore you use List.Intersect with the two tables to compare in list-format (curly brackets) as shown in line 3 above.

3. Transform all columns whose name are in a list to one specific type

Let’s close with the easiest case, which you’d probably be able to find out by yourself: Say your query returns a dynamic list somewhere with column names who then shall all be converted to a specific type:

You can directly reference the List as the first argument of the List.Transform-command.

You can download the file here: ChgTypeOfColumns.xlsx

Enjoy & stay queryious 🙂

 

Dynamic Power Query X-mas tree in Excel

Wishing my faithful readers peaceful and happy holidays.

Hope you enjoy my jumping Power Query X-mas tree: It changes his size or decoration randomly every time you press refresh: Totally auto-M-agically 😉 (No macros and no Excel formula in any cell ! )

Just 6 lines of code and 2 formatting conditions return a table with dynamic size. So if you haven’t discovered the M-agic of Power Query or Get&Transform in Excel yet, this is a good handful of code to start your discovery with!

XMasTreeV2.xlsx

Data -> Refresh All or Ctrl+Alt+F5

Happy holidays & stay queryious 🙂

 

How to store tables longer than 1,1 Mio rows in an Excel-sheet using Power Query and JSON

If you are working Power Query, you might come into a situation where you would like to make tables accessible from outside that file, that are longer than the 1,1 Mio rows who fit into an Excel-sheet. You can do that using the JSON-format and compression. That way you create a table in Excel that contains a JSON-text string, that can then be read and decoded again by Excel (or Power BI), using Power Query (or Get&Transform in Excel 2016).

You can download the file where a table with 2 Mio rows is compressed into a table with just 229 rows here: JSON_BreakRefreshChain4.xlsx

Simple idea:

Convert your table into JSON using the Json.FromValue-function. If we could export that JSON-file from here, we would be done already. But I haven’t figured out yet, if/how one could export or copy that from within the query-editor (apart from manual copy& paste, but that has also a limit of our good 1 Mio characters here). So if you have any idea about this, please share.

Workaround:

Read more

Dynamic duration calculation using DAX in Power BI and Power Pivot

While it is fairly easy to calculate the difference between 2 dates in DAX using DATEDIFF, it is a bit more demanding if you want to exclude weekends and holidays or filter the duration on certain date-intervals, so only get a part of it. Also if you want to return on date-time-level instead of only counting net-workdays.This is where this new technique for dynamic duration calculation can come in handy.

We can use the basic technique that I’ve described here and modify it by adding 2 columns to the calculated table:

  1. Duration per day on a Date-Time-level
  2. Marker-column if weekday or not (this assumes that you have a column in your date-table which indicates if the day shall be considered as weekday or not)

1_duration_calculation

The duration-calculation needs to handle the cases where only parts of the day are to be counted: If the event starts and ends at the same day, the difference between those figures has to be taken. If on the other hand, the event spans multiple days, for the start-day the time until the end of the day has to be calculated while for the end-days the time from the beginning of the day is the right one. The other days count as full days with 1. Hence these 4 cases.

Let’s have a final look at our simple measures:

Read more

Analyzing events with a duration in DAX – further simplification

Alberto Ferrari has recently published a very smart concept how to analyze events with a duration in DAX, which you should read here, if you haven’t done yet. It simplifies the necessary DAX-syntax and speeds up the calculations as well. My following approach simplifies the DAX-syntax even more, but it comes with a (very tiny) premium for performance and will also increase the file size a bit. So you have the choice 🙂

I’m transforming the calculated table into a “real” fact-table which enables me to use simple 1:n-relations to the other (now) dimension-tables:

T1datamodel

The formula starts from Alberto’s first version, but uses the Date instead of the DateKey (yellow). Then there will be some columns added which we need for following calculations (green). Then you see that the DailyProductionValue is calculated at a different place and also has a much simpler syntax. At last there are some other columns for further calculations: “Shipped” and “Ordered” will create the bridge for the “missing” connections to the date-table:

Read more

Import Measures from different Tabular Models using DAX Editor

Marco Russo has created a great tool for SSAS tabular that lets you edit measure definitions (which you should read here first if you haven’t done yet).

In this article I’ll show you how you can use it to import multiple measures from different tabular models into your current model.

The way the DAX-editor works is that it exports the existing measures from your model as a text file and imports them back after you’ve done your transformations. My technique will add the measures from the other model automatically to the existing measures so that both can be loaded back into your current model. In addition to that, you will have a UI with a process that guides you through the necessary steps that come with a task like that, which are:

  • select only those measures that you actually need
  • check references to existing measures and columns from the import model and manage their handling
  • allocate the tables into which these measures are going to be imported

This will all be done in a Power Query-powered Excel workbook that you can download here:   DaxEditorEditor_Final2.xlsm

These are the steps:

Read more