While Loop in M Language - In Short

[vojtechsima]

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.

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.

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
)

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

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

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.

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.

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.

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.