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? 🙂

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?»

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:

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:

Visual Basic

1

2

3

4

5

6

7

8

9

10

11

12

DimNewList AsNewList

CurrentValue=initial()

DoWhilecondition(CurrentValue)

Ifselector=nullThen

NewList.Add(CurrentValue)

Else

NewList.Add(selector(CurrentValue))

EndIf

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 functionevaluated 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 CurrentValueas 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. 🙂

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}asrecord

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):

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”:

In my mind List.Accumulate is one of most undocumented and most underestimated functions in Power Query ‘M’ language. In this post I would like to continue explanation of this very interesting iterative function.

The first (state) argument in accumulator sub-function has to be the same type as seed value only at first iteration and only if state argument is used in accumulator (I really see no sense in manipulations without using state argument, but you can do it, that’s why I noted it). After the first iteration the accumulator result will totally depend on what this sub-function does (as shown above).

The list elements could be used in accumulator sub-function. Could be not. You can use the list only as iteration counter – accumulator will perform its manipulations as many times as the count of list elements. This means that you can use List.Accumulate as “For Each … Next” loop (which has as many steps as a count of elements in list).

Let us try to look at how those features could be implemented.

SUMPRODUCT () in Power Query

For a starter let’s try to convert the famous Excel function, SUMPRODUCT, to Power Query function.

Why we really need it?

There are a lot of ways to implement SUMPRODUCT in Power Query. For example, we can add custom column with formula like this: = [Amount] * [Price] and then just get the sum of it. It’s easy and clear, except we need to add this helper column to get just one number.

But let suppose we have got two lists by some query manipulations and these lists are not columns in any table. We can transform them to tables, and then somehow combine those tables, perhaps with Index column, and… looks not as easy as it was with columns in one table?

How it works

Here we got two lists of the same size, MyList1 and MyList2

We’ll use one of lists as an list argument of List.Accumulate. We actually will use its elements in calculation, but also the count of list elements will be used as a counter to iterations inside our function.

As the seed argument we used a record: [Sum=0, Index =0], where Sum will be used as field for calculation and Index will be used as list’s current position counter.

We can describe the accumulator function as follows:

For Eachcurrent elementIn MyList1

Multiply current on MyList2{state[Index]} (where state[Index] is the value of Index from previous step or from seed if it is first step)

Add the result to the state[Sum] (where state[Sum] is the previous/seed value of Sum)

Increase previous/seed value of Index by 1

Next

At the end of the loop we have the result as a record of two fields: Sum and Index, as a result of Accumulate. We need only Sum field value, so we just add this filed name in square brackets at the end of row to get its value. Actually it means “give me the value of the [Sum] field from List.Accumulate result”.

SUMPRODUCT() UDF for PowerQuery/’M’

You can easily transform code from above to the one-row function for future uses: