Loop-de-loop or Loops without Loops?

If you come from Excel, you probably think of loops as VBA territory. If you’re from the SQL world, maybe cursors. But in Power Query? No traditional "for" loops, just functional transformations using lists.

That’s a feature, not a bug.

Today, we’re looking at list-based iteration. Which lets you run the same transformation across a list of values, pages, files, or sheets without writing a single macro.

Why This Is a Big Deal ?

• You can automate row-by-row or page-by-page logic using List.Transform, List.Generate, and parameterized queries.

• Unlike traditional loops, Power Query’s functional iteration is declarative. You describe what to do, and Power Query handles the rest.

• This approach powers paginated API calls, batch filtering, multi-file automation, and parallel logic (all with no need for scripting!).

Paginating Through a Multi-Page API

Let’s say your API gives you data 100 rows at a time and you want to fetch pages 1 through 10.

let

    Pages = List.Numbers(1, 10),

    BaseUrl = "https://api.example.com/data?page=",

    Calls = List.Transform(Pages, each Json.Document(Web.Contents(BaseUrl & Text.From(_)))),

    Combined = List.Combine(List.Transform(Calls, each _[results])),

    Table = Table.FromList(Combined, Record.ToTable)

in

    Table

You’ve just looped through 10 web requests, merged the results, and built a clean table using no loops, no VBA.

Applying a Query to Every Sheet in an Excel File

Got a workbook with 12 sheets (one for each month)? You can process them all in one go.

let

    Source = Excel.Workbook(File.Contents("Report.xlsx"), null, true),

    SheetList = Table.SelectRows(Source, each Text.StartsWith([Name], "Month")),

    Processed = Table.AddColumn(SheetList, "Data", each YourProcessingQuery([Data])),

    Expanded = Table.Combine(Processed[Data])

in

    Expanded

Just point YourProcessingQuery at a single sheet, and now it runs for every one.

Want a Real Loop? Try List.Generate

Need something closer to a classic loop (with a counter, condition, and output)? List.Generate is your go-to.

Example: Generate numbers until a condition is met.

List.Generate(() => 1, each _ < 10, each _ + 1)

Or use it to paginate until no results are returned.

Watch Out For...

• Complexity: Nesting List.Transform inside table logic can become hard to read. Use helper functions for clarity.

• Performance: List-based loops can trigger many HTTP requests or memory-heavy operations. Cache smartly and test at scale.

• Error handling: Use try...otherwise when calling APIs or processing multiple sheets, one failure can crash your whole chain.

In Summary

• Power Query doesn’t loop like traditional code—but it does iterate brilliantly using lists.

• Use List.Transform, List.Generate, and List.Combine to automate batch operations.

• Think in terms of transformation pipelines, not procedural loops—and you’ll build smarter, faster workflows.