Power Query Record as Dictionary: Part 1
How to use a special dictionary record for a performant lookup for values in Power Query
Continue Reading →
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 →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.
In Power Query we have special buttons for this:
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:
If we want to add three columns at a time, then we’ll also get a desired result:
But if in this table we want so sum columns [A] and [B], we are not expecting a pitfall, aren’t we?
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…
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:
|
1 |
= 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.
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? 🙂
Custom Lists Generator in Power Query and Power BI
List.Generate is the powerful unction of M language (the language of Power Query aka “Get & Transform” for Excel and Power BI query editor), used for lists generation using custom rules. Unlike in other list generators (like List.Repeat or List.Dates), the algorythm (and rules) of creation of successive element could be virtually any. This allows to use List.Generate to implement relatively complex get & transform tasks.
Although there are few excellent posts about this function uses (for example, Chris Webb, Gil Raviv, PowerPivotPro, KenR), I always I always lacked a more “clear” description — «How it actually works?» or «Why don’t it work?» and, at last, «What did the developers kept in mind when create this function?»
As usual, MSDN help article is laconical:
Generates a list of values given four functions that generate the initial value initial , test against a condition condition , and if successful select the result and generate the next value next . An optional parameter, selector , may also be specified.
Will you receive a list of four elements? Do you want to use an optional selector? Really? Why not?
In abandoned Power Query Formula Reference (August 2015) we can find the more clear description:
Generates a list from a value function, a condition function, a next function, and an optional transformation function on the values.
At least it is obvious that this function takes 4 arguments, all of type function:
|
1 |
List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list |
Actually List.Generate uses quite simple loop algorythm. When creating an element of a new list, List.Generate evaluates a some variable (lets call it CurrentValue), which then passed from one argument-function to another in a loop:
- Start value CurrentValue is the result of initial function evaluation.
- Pass CurrentValue to condition function, check the condition and return true or false.
- Ifcondition = false then stop list generation.
- Ifcondition = true then create next element of the list with this rule:
- If selector is passed to List.Generate and not is null, then pass CurrentValue to selector and evaluate its result.
- Else (no selector at all or it is null) then the next element is equal to CurrentValue.
- Evaluate next function with CurrentValue argument, and assign it’s result to the CurrentValue, so the new CurrentValue is evaluated next(CurrentValue).
- Loop to Step 2.
As you can see from this not-so-technical description, the important difference of List.Generate from other iterator functions of M language is that almost all of others working in “For Each…Next” style (they have a fixed list to loop over), while List.Generate uses other logic – “Do While…Loop”, checking the condition before loop iteration. Subsequently, the number of elements in created list is limited only with “While” condition.
If we’ll write down the algorithm described above in other, non-functional language (like Visual Basic), it will look like that:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Dim NewList As New List CurrentValue = initial() Do While condition(CurrentValue) If selector = null Then NewList.Add(CurrentValue) Else NewList.Add(selector(CurrentValue)) End If CurrentValue = next(CurrentValue) Loop |
Please note:
- initial funciton has no arguments and its evaluated value is equal to its excression value.
Even when you try to write the initial function with arguments you cannot pass any argument to it, because it called somewhere inside of List.Generate.
To be honest I do not understand why initial IS a function but not a simple expression or value. May be there are reasons for it. - initial function evaluated first
But, if the first call of condition function will return false, a list will not be created despite the initial function was evaluated.
In case of condition result is true then evaluated initial (or evaluated selector) will be the first element of the list. That’s why initial and next usually return same-structured values of the same type. - condition, next and selector got evaluated CurrentValue as an argument, but they don’t have to use it. Actually these three functions clould ignore CurrentValue, and use some other logic behind.
But, to be honest, I can’t imagine a situation when condition (or next ) do not use CurrentValue, because it leads to endless loop or list won’t be created. - selector evaluated despite of the result of next evaluation on the current loop iteration.
- next always evaluated BEFORE the subsequent list element will be created (2nd and following).
When you create a list using some API calls (for example, you send GET or POST requests to API in initial and next functions), you should consider the following:
- API will be called at least once (when initial is evaluated).
- The number of API calls will always be at least one more than number of elements in created list (this excessive call is the result of the last next function evaluation, which didn’t passe the condition)
It is convenient when both initial and next return value of type record. This greatly simplifies the addition of counters and passing additional arguments for these functions (for example, one of record fileds is main data, second is counter, etc.).
Resuming, List.Generate is the powerfull tool, looking more complicated than in fact. Hope this post made it more friendly and comprehensible. 🙂
Power Query Ideas on Uservoice and Power BI Communities
Recently I posted about UsedRange trap when importing from Excel sheet in Power Query (you can read this post there).
To add an option for users who can meet the same problems as me I’ve added corresponding Power Query improvement ideas on excel.uservoice.com and https://ideas.powerbi.com
Please add your votes to these improvements – I’m sure they can not only save you a few rows of code but also can save you from potential pitfalls when working with raw Excel data.
Thank you!
Excel sheet as a source to Power Query and Power BI: a pitfall of UsedRange
When you import data from an Excel workbook to the Power Query or Power BI from entire sheet, be careful, there is a pitfall.
After linking to an external Excel file there are three options of data extracting available:
- From table (table-formatted range of cells in the sheet),
- From custom named range of cells,
- From entire sheet
In the first case, a Table object is already structured data with columns’ names, automatically transformed to PQ tables. In the second case, Power Query shall give the named range generic titles (Column1, Column2, etc.) and then work as before.
However, it is often the case that data are not structured in a formatted table or named range, and it can be difficult to transform them to such view before import. There can be many reasons for that, e.g., cells format is to be saved (merged cells are no longer merged after transformation) or there are too many files to transform them manually.
Data on a “raw” sheet
Fortunately, Power Query can extract data from the whole sheet. To get data from unformatted sheet you do not need to perform any special actions: just connect to the file, find the needed sheet (it will have “Sheet” value in the [Kind] column) and get data by retrieving its content from the [Data] column:
Excel sheets available as data sources just as tables or named ranges
The question is: what data range will be retrieved in that case? There are 17 179 869 184 cells on an Excel sheet (16 384 columns and 1 048 576 rows). If Power Query try to get them all, there will be huge memory consumption and performance leak. However, we can ensure that usually number of imported rows and columns is about the same as the number of rows and columns with the data may be slightly bigger.
So how Power Query defines a data range on a sheet? The answer is out there if you familiar with VBA macros and have enough experience with an Excel object model (but I think you will not be glad with this answer).
On lazy value evaluation order in Power Query and Power BI
‘M’ (a Power Query Formula language) is a very powerful and interesting, but sometimes it could be a little confusing because of its lazy behavior.
Yesterday a friend of mine asked me for help with custom API connector in Power Query / Power BI. This connector has to work as follows:
- Post report requests
- Wait for reports completion
- Download reports
On the step 2 my friend used Function.InvokeAfter() built-in Power Query function, which supposed to pause code for a few seconds. But his code didn’t worked as expected – It looks like there are no requests posted at all.
We’ve found a solution to his task, but to make a long story short, there is what I have found from this case.
Let us look at this short code:
|
1 2 3 4 5 |
let a = DateTimeZone.LocalNow() - b, b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in a |
As we can imagine from ‘M’ evaluation rules, to get “a” we need to calculate “b” first. We can suppose that before evaluation of value “a” there goes value “b” evaluation. So, we’ll get “b” (current time), then extract current time from the same current time and get zero.
No way. The result is about -2 seconds: -00:00:02.0183210
Why? It is not so obvious, but there is a very easy answer: ‘M’ evaluates a value from left to right. When ‘M’ check for the a expression, it calculate first part:
|
1 |
DateTimeZone.LocalNow() |
Then it found “b” and evaluate it:
|
1 |
b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) |
The result of “b” is the local datetime with 2 seconds delay, so it is two seconds later than “a”. Of course, a – b approximately equals to -2 seconds.
It is easy to check:
|
1 2 3 4 5 |
let a = b - DateTimeZone.LocalNow(), b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in a |
There I changed evaluation order in the “a” expression, so now “b” is evaluated first, then second part ( DateTimeZone.LocalNow() ) is evaluated. As this evaluation is very quick, we have no delay and got the same time as in “b”. The new result is 0, zero.
So, what I have found here is that relatively complex expressions in ‘M’ evaluates from left to right.
There is another interesting result. Let us see the next code:
|
1 2 3 4 5 |
let a = DateTimeZone.LocalNow(), b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in { Duration.Seconds(a-b), Duration.Seconds(b-a) } |
The result of this code should be a list with two values. What I expected from previous example? There should be something like {-2, 0}, like results of previous calculations.
No way. The result is {-2, 2}:
Why there is a different result?
The reason is the lazyness of ‘M’: when the first element of the list evaluated, then it works like in example above: evaluate “a” then evaluate “b” (plus 2 sec), extract “b” from “a”. But for the second element ‘M’ did not make any evaluations of “a” or “b”. They already evaluated, so, as “b” is bigger than “a” for 2 seconds, extraction gives me +2.
If I change the order of the list elements:
|
1 2 3 4 5 |
let a = DateTimeZone.LocalNow(), b = Function.InvokeAfter(DateTimeZone.LocalNow, #duration(0,0,0,2)) in {Duration.Seconds(b-a), Duration.Seconds(a-b)} |
The result will be {0, 0}:
Now it is expected – values didn’t recalculated
Now I can easily explain why: when evaluating the first element, b-a, the “b” evaluated first, then “a” immediately evaluated and it equals to “b”, and we get a zero as the result of extraction. Already calculated “a” and “b” then swap their places and give us the same result.
It looks as a very interesting finding for me. I think I have to keep it in mind when I’ll try to implement some time-delayed evaluations in queries.
Quick Filter for Unique Values in Power Query and Power BI
There are many ways to get a value from parameters table and then pass it to query. One of this methods uses direct selection of unique parameter name. I think it worth a post.
Items selection: brief reminder
As I described in my first ever post “Absolute and Relative References in Power Query”, when we would like to refer to a single item in a list or a cell in a Power Query table, we can use Name{Argument} syntax: TableName{Row} or ListName{Element} . If Name is a table or list, and Argument is number, then it is simple: we asking for a row or element of such position.
The most commonly used syntax for single cell addressing in tables is
|
1 |
Table{RowNumber}[FieldName] |
But it is often omitted that if Name is a table, Argument could be not a number but a record:
|
1 |
Table{Record} as record |
Quick filter for unique values
Value passed as Record in this expression works like a filter for a matching field in the table. For example, [empl_name = “John”] .
How it works and what is in it for practical uses?
If
- our table has a column named empl_name
and
- row with value “John” in this column could be found,
and
- this row is unique (i.e. there is only one row with value “John” in column empl_name , matching to the Record ),
then entire record (i.e. row) will be returned as the result of this expression. In other words, a unique matching record from the table.
But there is one important restriction: if there is no unique matching row in the table, an error is raised.
So, when Table{Record} returns a desired result, it has type of record and we can reach a single item from this record by referring to desired field in it:
|
1 |
Table{Record}[Field] |
We can see this method in action when we build query to a table from Excel file. Power Query will create such string of code automatically:
|
1 |
= Excel.CurrentWorkbook(){[Name="Parameters"]}[Content] |
How Power Query refers to a table in Excel workbook
Implications
For example, we would like to implement table for passing user-defined parameters to Power Query (I recommend this great post “Building a Parameter Table” by Ken Puls):
|
1 2 3 |
myTable = #table(type table [param = text, value = text],{{"Name","John"},{"Department","Sales"}}), person = myTable{[param = "Name"]}[value], dept = myTable{[param = "Department"]}[value] |
In this example in the first row we create a parameters table with columns param and value that is supposed to be a parameters table for use in other queries.
Hardcoded parameters table (just for sample)
Then we got a parameter’s value by applying “record filter” and selecting desired field ( [value]):
Now we can quickly get “Name” parameter’ value from the table above
And “Department” parameter’ value from the table above. Any unique parameter.
What are the benefits in this approach?
- First of all, we do not need to remember a desired parameter row when coding (what if user swapped rows in parameters table?).
- Then, we do not need to filter parameters table each time to get a desired value.
- And also we can check for parameters table integrity – if there will be several rows with the same parameter name, or missing parameter value, or missing field, then we’ll got an error and can handle it.
And, don’t forget that this Table{Record} method has a lot of other uses – when we really need to get a record as a result of expression. Also we can pass a more complex (with 2 or more fields) record as filter.
And Argument record could be a result from other queries. Lets name this query as “QueryRecord”:
|
1 2 3 4 5 |
let Source = #table(type table [Name = text, Department = text, Region=text],{{"John","Sales","AL"},{"Jim","IT","FL"}}), QueriedRecord = Record.RemoveFields(Source{0},{"Region"}) in QueriedRecord |
We’ll take a record from this table
The result of above query is a record with two fields: [Name = "John", Department = "Sales"] .
I leaved only needed fileds in this record
Then we pass it as an filter argument to other table:
|
1 2 3 4 5 6 |
let Source = #table(type table [Name = text, Department = text, Date = text],{{"John","Sales","01.01.16"},{"Jim","IT","02.01.16"}}), ChType = Table.TransformColumnTypes(Source,{{"Date", type date}}), Filtered = try ChType{QueryRecord} otherwise null in Filtered |
We will filter this table with the result of previous query
The result is a desired record:
|
1 |
[Name = "John", Department = "Sales", Date = "01.01.2016"] |
Hey-ho, it works!!!
Another one “Get parameter” function
And a cherry on the cake – quick parameter selection function. You can easy change it for your needs:
|
1 2 3 4 5 |
(param_table as table, param_name as text) => let fnParam = try param_table{[param=param_name]}[value] otherwise null in fnParam |
You can also find a complete description of item selection in “Microsoft Power Query for Excel Formula Language Specification” (see “6.4.1 Item Access” on page 59 in August 2015 edition).
1
2