Search code examples
while-looppowerbim

Power BI while loop

I'm trying to do a while loop in Power BI M language. But all of the logic are all over my head!

How would you translate a very simple loop like this into M language?

while X == True: do abcdef if Y == True: end

Thanks very much!

Solution

  • Loops in M are probably best handled with the List.Generate function.

    This article does a pretty good job at explaining how it works:
    https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/

    Using this function, let's look at a more specific implementation of a while loop, say to find Fibonacci numbers less than 1000.

    a = 1
b = 1
while b < 1000
    b = a + b
    a = b - a

    would translate to M something like this:

    let
    data =
    List.Generate(
        () => [ a = 1, b = 1 ],
        each [b] < 1000,
        each [ b = [a] + [b], a = [b] ]
    ),
    output = Table.FromRecords(data)[a]
in output

    I'm not sure the best way to handle your break condition Y. It might depend on the specific problem.