All iteration roads in M Query eventually lead to the List.Generate function. In strengthening our data manipulation skills through Power Query, we absolutely need this looping function that has the classic paradigm of instantiation, termination condition, incrementing or decrementing to satisfy the termination condition and returning the results of the looping. If you have been getting by in Power QI without this function, you have been doing just that, “getting by”!
There are a multitude of articles that explain List.Generate on the web but this article introduces its capabilities with an off-beat scenario, with texts instead of numbers, to expand usage possibilities and emphasize how easy and yet powerful a function this is.
The return value of List.Generate is a list but there is no limitation on what the list can contain, such as records, lists, numbers, text, dates and so on. In the scenario below, we will generate a list of strings.
Understanding Our Data and the Goal
You can find the input file and solution here. Grab both the Power BI (PBIX) file (Nursery Roundup.pbix) and the file containing sample nursery catalogs (nursery_catalogs.xlsx) and store them on your computer.
If working your way to the solution, start with provided Power BI file and then:
- Open up the Power Query Editor (Transform Data will get you there).
- Change the source of the query Nursery Catalogs, to pick up your storage location of the input file.
3. Delete the existing query Search Nursery Catalogs, since this is what I will be walking you through.
[Note: If just following along the provided solution, don’t delete this query, instead follow the steps of this query and you can skip Step 4 below]
4. Create a reference to the query Nursery Catalogs as seen below and rename the query to Search Nursery Catalogs.
You will see that the input file contains a set of nursery names in the first row followed by a short list of peonies and dahlias available at the nursery. This data is stored and cleaned in the query Nursery Catalogs. The cleaning steps use the modularization and iteration techniques introduced to you in the previous article.
[Note: We add a column to index the catalogs. This column also helps with iterating through the columns. Lists in M query have a zero based index. We take note of this since we then know the termination condition will stop one short of the length of the list.]
With this catalog in hand, we are in search of a couple of flower species which we enter into the query Flower Basket using the Enter Data option in the menu. We are in search of Sorbet Peony and Little Scottie Dahlias and want to make just one trip to the nursery, so we can spend the afternoon digging and planting both bulbs.
Goal : The goal is to search for both these flowers in this query (Flower Basket) through the columns of Nursery Catalogs query and locate a nursery that carries all flowers in the basket. The output expected is seen below
Code Walkthrough for Query Search Nursery Catalogs
In the first three steps, we name the table Nursery Catalogs to nursery_table, a) Create a list of its column names using Table.ColumnNames, named nursery_names.
b) Convert our flower basket to a list of strings, using Table.Column, named flower basket.
And as mentioned in the previous article, avoiding space in the step names makes the code much more readable by minimizing the distracting # and quotation marks.
With the above two lists, we set out with List.Generate which, as seen in the code above has three required elements and a fourth conditional element. It returns a list of either the fourth element, if specified, or the list of all the “next” elements generated in each iteration by default.
List.Generate(initial as function,
condition as function,
next as function,
optional selector as nullable function)
as list
Initial Function
Initialization is with a lambda function, that in this scenario creates a “record” of elements. We need four items to move through the loop.
1) A counter that serves as an index to step through all the nurseries (columns in the table Nursery Catalogs). This is initialized to 0, that references the first catalog number column that will not contain any flowers.
2) The list of column names in the table Nursery Catalogs (we call it x).
3) The list of flowers in our basket (we call it y).
4) The result which is our determination of whether the items in our basket list can be found in a nursery column. This is initialized to “False”, since the first column (index 0) will never contain any flowers from our basket.
// 1. Initialize a record
() => [counter = 0,
x = nursery_names,
y = flower_basket ,
result="False"
],
Termination Condition
We step through each column and stop before length of column list. Since the column list is six elements long, the counter will terminate at 5 which is the index of the last column in the Nursery Catalogs table. Remember, lists are zero-indexed.
// 2. Set a termination condition
each [counter] < List.Count([x]),
Next
The record that is created here serves as input to the next level of iteration. The counter is incremented to grab the next column of the table, the column names and list of strings from the basket are preserved and the result of searching the current column for items from our basket is stored.
Here are the first three elements of the Next record being set up.
// 3. In each iteration update record that will be passed to next //iteration //The counter loops through all columns of the table and is //incrementedeach [counter=[counter]+1 , // x and y stay the same x= nursery_names, y=flower_basket,
This is the last element of the Next record being setup.
//For each column, check if any flower/string from our basket is in // the column result= if List.ContainsAll(Table.Column(#”Nursery Catalogs”, [x]{counter}), y) then “True” else “False"],
The List.ContainsAll function returns logical true if and only if ALL the strings in the basket are found in the current iteration column. The counter is used to index the column list x. [x]{counter} accesses the ‘counter index’ element of list x. The values of the column are accessed using Table.Column function.
The list of flowers “y” is compared with all items of that column’s values. If all the flowers strings are found in a column, then List.ContainsAll returns True that we convert to result string “True” else the result is set to string “False”.
Result
We are only interested in knowing if a column contains the strings in the basket and so we include the optional fourth parameter to the List.Generate and return only the “result” element of the records.
// 4. Return a value for each iteration
each [result]
),
In the above list that is generated by List.Generate step, that has been named contains_flower_col_list, you can see that the last column (list index = 5, since it is zero based index) contains both flowers from our basket.
Harvesting the Result
Now that we have the column(s) that contain the flowers we are looking for, we can retrieve the first column (nursery) that carries these flowers using the index returned by List.PositionOf. It will serve as an index element of the column names list from table Nursery Catalog to convert the index into the name of the column.
{List.PositionOf( contains_flower_col_list, "True")}
The above will return 5 in our case, and placing it in curly braces after a list is how you can access an element from the list using the index value in the curly braces. This helps us pick out one nursery containing all items from the basket.
nursery_col_name =
if List.PositionOf( contains_flower_col_list, "True") > 0
then Table.ColumnNames(#"Nursery Catalogs"){List.PositionOf(
contains_flower_col_list,
"True"
)}
else "Nursery Not Found",
Error Handling
Now that we have the name of the nursery, we can access that column from the Nursery Catalogs table.
There is a possibility that no nursery contains all items from our basket. In this case as seen above, we return “Nursery not found” . This is not the name of any column in the Nursery Catalogs table. So we wrap the retrieval of the column values in a try…otherwise.
= try
Table.Column(nursery_table, nursery_col_name)
otherwise {"Nursery Not Found"}
Final Step
To present the final query to the user, we convert the nursery column’s catalog list to a table and assign the captured nursery name as column name using Table.FromList.
Exercise
And now it is your turn. What if more than one nursery carries your selection of flowers you want to plant this Spring? Can you create that display?
I also encourage you to take a deeper look at the scope of variables used in List.Generate within a “let” and within a function call here and as elaborated in this article.
