While Loop in M Language

tl;dr Power Query M language doesn’t have a while loop as you know it, but it does have a function called List.Generate() that can help you achieve the same thing. This article explains what a while loop actually is and how to mimic that behavior in M.

While Loop in M Language is sus

Yeah, it is. Power Query M language doesn’t have a while, for, or other classic looping keywords you might be used to in other languages. However, if you’re transitioning from a language with these keywords to the simpler M language, or if you just like thinking in the traditional programming way, M still offers ways to express your logic similarly.

What's a while loop

If you were sent here randomly by the M language gods and have no idea what a while loop is, lemme break it down for you. A while loop is a technique that controls the flow and repeatedly executes a block of code based on a boolean condition. In other words: while something is true, do something.

For a real-life, non-computer example: imagine you’re playing a little game with your friends to see who can roll a six on the dice in the fewest tries. That’s a while loop—you keep rolling the dice until it lands on six.

While the dice doesn’t show six → keep rolling.

For the real-life, computer example: you’re running a query against a slow SQL database. You keep querying while the total processing time is still below the timeout limit.

While processing time < timeout → keep querying.

How to do it in M language

Okey, let’s see how you can actually pull this off in M. Depending on what exactly you’re trying to achieve, there are a few ways to go about it. The closest thing to a traditional while loop is the List.Generate() function. This function literally has a while true parameter baked right in.

Let’s break it down.

List.Generate()

List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list

List.Generate() takes an initial value (any value → number, record, list, etc.) and keeps returning that value (or the updated value on each iteration) until the condition is true.

Generally, you want to modify your initial value with each iteration so it can eventually meet the condition.

You can also use the current iteration value to trigger something else, like pass it to another function, transform it, or simply return a string if that’s what you need.

Let’s see how you’d model our throwing a dice example in M language.

List.Generate(
 ()=> 0,
each _<> 6,
each  Number.Round(Number.RandomBetween(1,6), 0)
)

Commented:

List.Generate(
 ()=> 0, //initiate the starting value
each _<> 6, // keep throwing until the value is 6
each  Number.Round(Number.RandomBetween(1,6), 0) // upon each iteration, generate new number
)

Output

If you look closely though, this code has some obvious flaws. First of all, the initial value shouldn’t be zero—the first iteration should already be a dice throw. The rest works, but when the number is six, the condition stops the loop before adding that final six to the list, which can be annoying not to see. Lastly, we could also add a counter to track how many throws it took, and technically skip outputting the whole list by just returning that information as text.

Let’s see if List.Generate() can handle our quality improvements:

let
	rollDice = ()=> Number.Round(Number.RandomBetween(1,6), 0),
	throwDice = 
		List.Generate(
			()=>  rollDice(),
			each _ <> 6,
			each rollDice()
    		)
in
	throwDice

So we didn’t really do much inside List.Generate() itself. The only thing we did was introduce a variable—or rather, a function—that handles the dice roll, so we don’t have to type the same thing twice inside List.Generate(). The rest stays the same. Now we have our counter finalized. We don’t need to introduce any record to track the count, because the count will always be the number of items plus one (since it stops when it hits 6, but doesn’t add that final roll to the list).

List.Generate() always returns a list (or error), if you want to return a simple string/text, you have to wrap it with a different function.

let
	rollDice = ()=> Number.Round(Number.RandomBetween(1,6), 0),
	throwDice = 
		List.Generate(
			()=>  rollDice(),
			each _ <> 6,
			each rollDice()
    		),
	resultText = "It took you " & Text.From( List.Count( throwDice) + 1 ) & " throw/s to roll number six!"
in
	resultText 

Result as text

I know, you could handle the "throw" with condition, but I trust you that you can do that part yourself.

Limitations

Technically, List.Generate() doesn’t have a set limit for the number of iterations. The real problem is memory usage. If you had infinite RAM, you could technically run an infinite loop forever.

That said, I highly recommend not building any infinite loops—especially in Power BI Service, where you’ll drain your capacity pretty quickly. If you just want to experiment and see how it works, do it locally where only your RAM takes the hit.

For example, I tested how much memory it would take to run an infinite loop that spams "kitten" or just generates an infinite list of ascending numbers.1 billion rows? Took just a few minutes and ate up around 40 GB of memory. Effortlessly.

Infinite loop that returns the string "kitten"

If you translate this code to plain English, it’s basically:While true is true → return "kitten".

Data-driven loops via List.Generate()

Data-driven loops (unofficial term, don’t quote me) are loops that rely on an external source to return additional data. In our rolling dice example, we were simply counting the number of iterations, but in the real world, you often have to perform several transformations inside List.Generate() just to get close to your “break” condition.

Some examples of this can be:

• An API that returns a next page URL you have to call to get more data

• A daily file fetch scenario where you keep looping and collecting files until your total file size hits a set limit

Example #1

Let’s start with a classic use case: implementing data gathering from a REST API with cursor-based pagination. In this example, it's gonna be Power BI REST API Get Activities.

For more in depth explanation (covering all subparts), check my other blog post where I explained this technique in token-based pagination.

I’m gonna make a couple of shortcuts here and only explain the while loop part. Custom functions such as request and nextPageRequest are already defined earlier. Let’s look at the loop part.

Functions, for clarity, are not initiated through the typical each keyword, but instead by defining a function with a parameter i, which stores the current iteration’s record.

Everything is handled by List.Generate(). Here we establish the first parameter as the initial request. The initial request returns the data of the first page and, if there’s another page, it gives us the next page in the continuationUri field. We control our iteration with a record containing the fields request, next, and isLast.

The second parameter simply checks whether the current iteration’s response is the last page.

The third parameter drives what happens for the next iteration. Here we want to create the same record, just let’s say one iteration lagged behind. We have to lag behind because we set up the condition—the while part—to check if lastResultSet is set to TRUE in the last page, so it would stop before proceeding with the last page’s data.

The last, optional fourth parameter—called the selector—then just grabs the current iteration’s actual data from the request, specifically from the activityEventEntities field.

List.Generate(
        ()=>  
        [
            request = request(#date(2025,5,11), #date(2025,5,11)),
            next = request[continuationUri]?,
            isLast = request[lastResultSet]?
        ]
        ,
        (i)=> not i[isLast]?,
        (i)=> 
        [
            request = nextPageRequest(i[request]?[continuationUri]?),
            next = i[request][continuationUri]?,
            isLast = i[request][lastResultSet]?
        ],
        (i)=> i[request][activityEventEntities]
     )

If you want, you can define the request field of the third parameter like this: request = nextPageRequest(next), which will do the same thing. But I felt the way I set it up is easier to understand.

Example #2

Let’s imagine you’re building a report to monitor which files contribute first to hitting a daily file size cap. Let’s say 500 MB. You suspect that the early large files are clogging up your system in the morning and slowing down production.

Your source is an API that collects file metadata for each day. You’re gathering sorted data, trying to track which files get you to just under that 500 MB threshold. If the last file would push you over the limit, you don’t want it included. The idea is to catch only the files that comfortably fit within your cap and analyse them further.

Let's look at how we can build it with List.Generate().

resultWithSelector = (filesList as list, fileSizeField as text, sizeCap as number)=>
List.Generate(
        () => 
        [
            index = 0,
            item = filesList{index},
            runningMB = Record.Field(item, fileSizeField)
        ],
        each [runningMB] <= sizeCap,
        each 
        [
            index = [index]+1,
            item = filesList{index},
            runningMB = [runningMB]+Record.Field(item, fileSizeField)
        ],
        each [item]
    )

We designed resultWithSelector as a versatile function that you can call and adjust according to your exact requirements. It accepts three key parameters to control its operation:

• filesList (list): Represents your list of files.

• fileSizeField (text): Specifies the field within each file's record that holds its size, used to determine when the size cap is reached.

• sizeCap (number): Represents the maximum cumulative size allowed. The loop stops when the cumulative total size reaches or exceeds this threshold.

To use this function, you can execute the following call:

result = resultWithSelector(filesForDay, "SizeMB", 500)

Naturally, the exact implementation depends on your API’s endpoint setup. Here, we assume your files are pre-sorted and stored in a list (filesForDay), accessible by index. Given this sorted order, we sequentially walk through the list: each iteration grabs the next file, adding its file size to the cumulative total (runningMB).

We continue this process until the total size meets or exceeds the defined cap of 500 MB. The resulting output is a tidy list of records, each representing a file that contributed to hitting your specified threshold.

The result is a nice list of records where each item represents a single file:

Variable list of files

Summary

In today’s article, we went over how to mimic the traditional programming case of a while loop. Even though M language doesn’t have the actual while keyword, it does offer a function called List.Generate() that can pretty much act as one. A while loop built with List.Generate() can help you with simple loops, like the throwing dice example, or with more practical, real-life scenarios like fetching next pages in APIs or gradually pulling files until a limit is reached.

Naturally, this function can do more than just while loops—for example, for loops—but that’s a topic for another article. Thanks for reading.