Power Query List.Generate vs. VBA For...Next

When I read Bill's "How Would You Clean This Data" challenge couple months ago, I thought that it could be easily done by using VBA. However, the article was all about Power Query. I was interested in learning the M language. I was even more interested in comparing VBA with M language.

During the process of solving the problem using the M language, I realize that M should be easy to learn for anyone already using VBA. By using M instead of the Power Query interface, your Power Query powers will double.

In this article, you will find my way of cleaning the data by using the List.Generate function in the M language.

To refresh your memory of the original problem, the following is the source data defined by "UglyData" name.

The following is the desired result:

I am impressed by the power of Power Query. It serves the result as a query table in the worksheet. The user-friendly interface with simple buttons that enables anyone to transform your data and get the job done. I strongly believe that every Excel user should try Power Query to deal with data transformation at least once. It is simply amazing. But, if you limit yourself to the commands found in the Power Query ribbon, you will be using less than half of the entire power available in the tool. If you are a VBA programmer and you are interested unleashing the entire power of Power Query, it is easy to transfer your VBA skills to learn the M language. Soon, you will find you have unlocked more power in Power Query.

Do you remember when you were learning VBA? You likely relied on the macro recorder and the intellisense code completion. Early in your learning journey, the macro recorder likely saved you hours.

When you use the ribbon icons in Power Query, Excel generates M code just like macro recorder in VBA. The M code can be revealed by using the Advanced Editor in Power Query.


Power Query ribbon

However, for me, the auto-generated M code is hard to read. The auto-generated code uses long variable names including spaces - quoted identifiers.

Although function results are called as identifiers as they are actually uniquely identified in M language, I call them as variables since a variable is also an identifier.

Click to expand...

As a traditional programmer, variable names should never have spaces. Besides, quotes surrounding the variable names make it really hard to understand if it is a text value or variable. So, I defined my first rule while coding in M language - Do not use spaces in variable names and change the auto generated variable names immediately. Basically, use ChangedType instead of #"Changed Type".

When I first looked at the M code in the Advanced Editor, I noticed that it looks nothing like a procedural language. This makes sense since it is not a procedural but functional language.

The M code recorder builds the final code as steps that consist of variables. It uses the previous variable to create the next variable. Eventually it arrives at your final result. The recorded code looks like a strict structure. However, the M language does not care if you use white space or new lines while you are coding. This means what when you start manually coding in the editor, you can write your code just as you would write in VBA. The important rule is that you add a comma after each variable definition (except for the last variable).

This became my second rule - Treat M code like VBA code in the editor. It simply makes the code more readable. The code will have a familiar look for experienced VBA readers. You will also find it more natural to type code in this format.

The following code that I applied my rules works exactly the same with the auto-generated one.

My last rule is a little bit different that the first two rules - Find corresponding M functions for VBA methods.

VBA programmers are familiar with the For… Next loop in VBA. The equivalent functionality in M is List.Generate. You will see this structure used when cleaning the data in Bill’s problem. This way we can make a comparison between coding in two different programming languages.

Let's create a new blank query and copy and paste the following code in the Advanced Editor.

Now we have the source data from the worksheet range called "UglyData" in Power Query. It would create the same table if we loaded this query to a worksheet since we didn't do anything on it yet - except employee quarterly column headers since Power Query treats table columns as fields and field names must be unique, so Q1, Q2, Q3, and Q4 for each employee will be automatically suffixed with an incremental number following an underscore. So, let's start transforming data.

My intention is building two nested loops where the inner loop is looping through the category descriptions (rows) while the outer loop is looping through the employees (columns). The following VBA code shows how I would do the same thing if I was solving this problem by using VBA since the source data has predefined columns. So, I will be retrieving quarterly data for each employee and for each category from the corresponding rows and columns. It could be done in many different ways in VBA, and likely better than the code below, but I intentionally wrote it as below to make it look like the M code that I will write in this article.

We obviously need to get the employee and category count as well as the category names from the source data. Let's modify the M code to create corresponding variables.

The let expression simply encapsulates uniquely named variables consist of assigned and/or calculated values separated by comma. Each variable can be used in a subsequent expression to calculate another value defined as another variable. As you can see, we created three helper variables that we will use in the next steps below.

The Result variable is only for showing a list of these three helper variables by using List.Combine function in this step. In the screenshot below, I clicked on the last list element, CategoryNames, to reveal the list content consist of category names as it is also a list element in the generated list, and it won't be displayed like the first two primitive value types in the result list which are employee and category counts.


List.Combine to create a new list

Now we can build the outer loop by using the same algorithm as we did in VBA. The corresponding M function that will let us build a loop is List.Generate function. List.Generate doesn't look like the same For...Next statement in VBA but it is doing the iteration that we need.

Let's modify the M code to create a list of employee names retrieved from the source range. Remember that employee columns are placed in 7th, 12th, and 17th columns in the worksheet. We will use Table.ColumnNames function to get the column names in the source data, and return nth column's name from the list by calculating the n with i * 5 + 1 formula as 6, 11, and 16. Notice that indexes start from zero in M language functions as different than the actual column index numbers in the worksheet, so ColumnNames{6} is actually 7th column in the worksheet and so on.

And the result:


List.Generate to create a loop

Let's take a closer look at the List.Generate function. It takes four function values as parameters called initial, condition, next, and selector.

In M language, functions can be passed as parameters to other functions.

Click to expand...

• Initial function: () => [i = 0]
  • List.Generate starts with an initial function returning a record which contains the control variable field named i which is equal to 0 initially. This function is executed only once to assign starting values of the variables that will be used in the iteration and return a record. This record is then used in the scope of List.Generate function.
• Condition function: each [i] < EmployeeCount
  • List.Generate checks the condition function value on each iteration, and only execute the next iteration if this function returns true. Here we are using i field as the control value that will be incremented in the next function and continue looping while it is less than the employee count.
• Next function: each [i = [i] + 1]
  • This function sets new values for the variables that were assigned in the initial function which is only the control value hold by i field in our code and return as the updated record to be used in the scope.
• Selector function: each ColumnNames{[i] * 5 + 6}
  • Finally, the function that returns something meaningful from the iteration. We can omit selector function, in which case iteration will return the record assigned in next function contains the incremented value of i field. However, we need to select and return values from the corresponding columns of the source data, so we do not omit selector function in this implementation.

Some quick information about functions in case you are confused with the each keyword as I was at first. Take a look at the following M code.

(x) => x + 5 is the function in this assignment. (x) before the function definition symbol, which is =>, is where we include the expected parameters by the function, which is x in this function. The calculation after the function definition symbol is the return value of the function.

Result = fnMyFunction(10) is where we call the function as we assigned fnMyFunction variable as a function value. This query will return 15 as result.

So, it is now more obvious what () => [i = 1] initial function is doing in our actual query. Without taking any parameter, it is creating a record contains i field which will be used as the parameter by the other three functions in the scope of the List.Generate function. However, we do not see the same function structure in the other three functions, but each keyword. Therefore, it is time to talk a little bit about each keyword in M language.

The each keyword forms a function that returns an object by using the record in the current scope. And it is just a shortcut way of writing the actual function. So, the following function returns a boolean value according to i field value.

Let's rewrite the condition function without the each keyword and compare.

By using the each keyword, we simply create the function without using scopeDataObject function parameter as it is automatically injected into the function by the host function, which is List.Generate. This saves us using an unnecessary variable, makes the code more readable, and probably also some memory at the background.

There is no similarity between each keyword here and For Each...Next statement in VBA.

Click to expand...

According to the desired result, we need to list all category names for each employee name. We have the employee names list so far. If we simply repeat employee names for each category name, and eventually add corresponding quarterly columns to the result, then we will get what we need. However, to return multiple columns, we need more than a list. Look at the following samples below.

A List simply is a column in Power Query.

This query creates the following list with three string values.


Create a basic list

A Record is on the other hand, a row consists of named fields.

And this query creates the following record with three named fields.


Create a basic record

Notice that we use curly braces to create a list, and square brackets to create a record that contains field definitions and values.

What happens if we used records as list items? I will also include a Name field in the record this time.

This is a list of records just as expected, and beautiful! If we click on a cell, then we will see the content of the record.


List of records

Now, we have a list of records. All we need to do is convert the list to a table, and then expanding the record fields as table columns.

That's it!


Table.FromList and Table.ExpandRecordColumn

Let's apply this logic to our actual query now. Instead of creating the inner loop for the category names right away, I will write the code to retrieve only the first-row values from the source data, which is the "Administrative" category row.

Perfect! We have the result table for "Administrative" category. The next step is creating an inner loop to loop through all categories.

Before that, let's examine how we are building the record in selector function.

• Category = CategoryNames{0} - We set the Category field by selecting the first item in the CategoryNames list.
• Employee = ColumnNames{[i] * 5 + 6} - We already know how we get the employee name from the specific column in source table's header.
• Q1 = Record.Field(SourceWithHeaders{0}, ColumnNames{[i] * 5 + 7}) - This is something new, because we are reading from the record which is the first row of the source data. Record.Field function takes two parameters; first parameter is the source, the first record (source table row) here, and the second parameter is the name of the record field (table column header).

We are almost there. As you would guess, there is only one thing to do left, looping through the category names instead returning data for only the first category. We will do that by creating another List.Generate function as the selector function of the existing List.Generate function. This will be the inner loop for categories.


Let's look at the parameters of the inner loop.

• Initial function: () => [i = [i], j = 0]
  • This time we have two value fields that will be used in the function scope.
    • i is the outer loop control value. We assign i field value of the inner loop's record by using the outer loop's record control value. We are simply carrying over the employee counter inside the inner loop. It will be still incremented by the outer loop, but we need it in the inner loop to find the current employee columns, including employee name in the header and quarterly data.
    • j is the inner loop control value, and it is initially 0. It will be incremented in the next function just like it happens in the outer loop.
• Condition function: each [j] < CategoryCount
  • In the inner loop, we are using j field as the control value that will be incremented in the next function and continue looping while it is less than category count. Nothing different than the outer loop as we did for incrementing i field value.
• Next function: each [i = [i], j = [j] + 1]
  • We initiated two fields in the scope record in the inner loop and we need to pass them to the next iteration. We keep the i field value same because it is just carrying over the outer loop control value, and we increment j field value by 1.
• Selector function: Q1 = Record.Field(SourceWithHeaders{[j]}, ColumnNames{[i] * 5 + 7})
  • Nothing much is different here. We get the category and employee names. We get the corresponding employee quarterly column values. As a difference, we are just selecting the current row instead of using only "Administrator" category by using 0 index.

Finally, the inner loop returns a list of records, and after necessary transforming steps, we have the desired result!

Thanks for reading! If you enjoyed this article, then please let me know.

This does not mean that Power Query does not have other functions to get the same result. You will see amazing methods in Bill's article that solved the problem nicely. The point of this article is to compare the M language and VBA from my point of view as a VBA programmer. And of course, providing an example for List.Generate function.

Click to expand...