Power Query Connection to Excel PowerPivot Data Model

TL;DR

We can connect almost any data source in Power Query, but PowerPivot data model is not included in that extensive list of sources out of the box.

But with the help of the fabulous DAX Studio we can do it (although in my opinion it is still inconvenient and tricky) at least locally – from the same workbook in Excel or from Power BI Desktop.

All you need is to open your Excel workbook, run DAX Studio add-in and connect it to this workbook. Then you can just connect to the PowerPivot model as to SQL Server Analysis Services cube.

But this is an undocumented and extremely limited feature not supported by Microsoft, which can only be used under your own risk.

Continue Reading Follow me: Facebooktwitterlinkedinmail

Classical Incremental Refresh For Cloud Data Sources in Power BI Service (for Pro accounts)

[TL;DR]

In this post I describe how to implement the classical incremental refresh scenario for the cloud data sources in Power BI Service for Pro accounts. Step by step. It worth to read.

Foreword

As I wrote in the previous post, we can implement a semi-incremental refresh for Pro accounts in Power BI, using just dataflows.

The method I described has a main lack: although the “historical” dataflow remains unchanged and will never load data from the source again, the “refreshing” dataflow will load the whole “fresh” part of data repetitively until you change the date intervals manually.

Initially – there’s small amount of data in the “fresh” part…

Fresh data become not so fresh…

…but, after some consequential refreshes, it could become significant, and not so fresh.

You can again split “fresh” it in the two parts – “new historical” and “fresh”, and so on. But this is only SEMI-incremental refresh, and, of course, is not a good solution.

It seemed that implement a complete, classic incremental update using just dataflows is impossible.

But, after some investigations, I found a solution which helps to implement the classical incremental refresh scenario, where the fresh data part remains small and fresh, and historical part become updated without querying a data source.

Fresh data remains fresh 🙂

At least for the cloud data sources.

Continue Reading Follow me: Facebooktwitterlinkedinmail

Video: Semi-incremental refresh for Pro accounts in Power BI

After I published previous blog post about an incremental refresh for Pro accounts in Power BI, Microsoft MVP Parker Stevens from BI Elite channel kindly asked me to record a video for his channel. So here it is.

Here I not only introduce Power BI dataflows and describe the semi-incremental refresh concept, but also show how it works in Power BI Service.
Have a fun!

To my surprise, it has almost 1500 views in two days – not so bad 🙂 I understand that this happened because of hype topic, but, well… now I know how to remove some limitations and perform a CLASSICAL incremental refresh for some types of data sources. Blog post follows.

Follow me: Facebooktwitterlinkedinmail

Incremental Refresh For Pro Accounts With Power BI Service Dataflows

Incremental refresh is a high-demand option in Power BI. Microsoft already provided it for the Premium capacities, but for the Pro accounts it is still in waiting list.

However, with introduction of dataflows in Power BI Service, an incremental refresh implementation becomes available for Pro accounts too.

I won’t to describe dataflows in detail here since there is a lot of blogs and resources about it (but I’ll provide a few links in the bottom of the post).

Concept

All you need to know now on how to implement an incremental refresh, is that

  1. Dataflow in Power BI Service is a set of web-based Power Query queries (named as ‘entities’).
  2. Each dataflow could be refreshed manually or by the its own schedule.
  3. The result of evaluation of a dataflow’s entities then stored in Azure Data Lake Storage Gen2 as tables (more precisely as CSV files).
  4. Then you can use dataflows (their entities) as a data sources in your Power BI dataset.

Let’s start from this point.

What is the incremental refresh at all? In simple words, it means that in the single data import action we are refreshing (updating) only the part of data instead of loading all the data again and again. In the other words, we are dividing data in two parts (partitions): first part does not need refresh and should remain untouched, second part must be refreshed to bring in updates and corrections.

Continue Reading Follow me: Facebooktwitterlinkedinmail

Correctly Sum Two Or More Columns in Power Query and Power BI

Let’s say you have a few numerical columns [A], [B] and [C] in your table and want to sum them to the new column in Power Query or Query Editor in Power BI.

Three numerical columns we want to sum in the new column

In Power Query we have special buttons for this:

Sum of columns in Power Query is easy as 1-2-3

For example, we want to sum columns [A] and [C]. Just click (holding Ctrl button) column headers you want to sum, then go to “Add Column” – “Standard” – “Add”, and you’ll get a new column named “Addition” with the row-by-row sum of desired columns:

Sum of columns [A] and [C] – sure it is

If we want to add three columns at a time, then we’ll also get a desired result:

What we’ve expected? Just simple sum of [A]+[B]+[C]

But if in this table we want so sum columns [A] and [B], we are not expecting a pitfall, aren’t we?

What could go wrong?

The reason of this behaviour is simple and it reveals itself when we look at our data a little bit close: there is a null in column [B] in that row. In Power Query formula language (M) the expression null + value always returns a null (see this excellent post of Ben Gribaudo about null type and operations with null values).

But why we get a correct result when we sum up three columns? It is because Power Query uses different formulas when we sum two columns or three and more columns:

List.Sum function used in this case ignores null values and sums up only numerical values. Indeed, it gives more intuitive result, but on the contrary has not such intuitive syntax of simple addition.

I do not know what is the reason of such difference, and already complained to the development team. But if you rely on the buttons there, then you have to be aware of such behaviour.

What is the possible solutions there? It depends on what you want to get as a result, but in any case you should take a look at the formula bar and decide what to correct there:

  • If the logic of your calculations assume that value + null = null, then you should use simple + symbol between column names.
  • If you want to get value + null = value, then you should use List.Sum finction, like in that example: List.Sum({[A], [B], [C]})

THE SAME BEHAVIOR Power Query shows when you’ll try to multiply two columns and three or more columns: with two columns there will be the simple * symbol, with three or more columns there will be List.Product function used.

Ok, it is a really short post which I planned to (and ought to) write a long time ago…

Follow me: Facebooktwitterlinkedinmail

Comparing ‘null’ values in Power Query

Recently I needed to do the very simple thing in Power Query. I have the column of numbers and need to check if the values in this column are less than N and then put a corresponding text value in the new column. The function for the new column is something like this:

= if [Values] < 5 then "A" else "B"

Actually some values are not a numbers but nulls:

Data contains nulls and comparison return an error

And this simple calculation returns an error for these values!

Why? There is the catch, which is hidden in the depth of documentation (actually on the page 67 of the “Power Query Formula Language Specification (October 2016)” PDF which you can obtain there.

Continue Reading Follow me: Facebooktwitterlinkedinmail


ISO Week in Power Query / M language and Power BI

This is a very short post, just to make a reminder and possibly expand knowledge for me and my readers.

Sometimes, specially when working with calendar tables, we need to calculate ISO Week Number for certain date. There is no native functions in Power Query / M language / Power BI to get ISO Week number, so to obtain the desired result you need to write your own function.

Thanks to Catherine Monier, Microsoft Excel MVP, for providing the link to the “Date to ISO Week” M function already written for us. There also another function for converting ISO Week date (format input like: `2017-W02-7`) to the normal date:

This function is not so hard to develop, I think, but it is always better when somebody gives you ready-to-use solution, isn’t it? 🙂Follow me: Facebooktwitterlinkedinmail