Incremental Load in Power BI using DAX UNION

by

This is an old post. Meanwhile we can implement proper incremental refresh with Power BI licenses: https://docs.microsoft.com/en-us/power-bi/admin/service-premium-incremental-refresh Warning – edit 03-Jan-18: This method doesn’t work in the service unfortunately. Please upvote the bugfix here: https://community.powerbi.com/t5/Issues/quot-Include-in-report-refresh-quot-disabling-doesn-t-work-in/idi-p/331982#M19945 This article describes the latest workaround for incremental load in PBI (thx to Taylor Clark for stressing this out!). It’s not very dynamic, … Read more

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

by

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 … Read more

Dynamic Power Query X-mas tree in Excel

by

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. … Read more

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

by

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

by

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

by

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