Creating Loops/Iterations in Power Query

Ever wondered how to iterate over a series of objects in Power Query? Let’s have a look on that. In this article, I will be covering how to create loops/iterations in Power Query. In our sample example, we will have a simple flat table with 3 columns: Country, Variant, Clicks. The goal is to determine which variant wins=has the highest number of clicks in all the 4 countries. The example is based on 13 rows only but it would be no different if there were 13,000 rows with more countries and variants.

Don’t want to lose time on replicating this exercise? Download the complete sample file with the loop trick used in this article:  
DOWNLOAD NOW FOR WINDOWS

You might be already thinking that creating loops can help to e. g. easy select winning ads across several thousand ad groups when it comes to PPC. You are right 🙂

This is our input table:

Note that there is no special …for…in… statement in Power Query unlike in Python, for example. The main trick in Power Query is to use “Group” function wisely and to apply the right set of functions over the grouped objects.

Disclaimer: Loops are also possible to be created via List.Accumulate function but that’s rather for super advanced users. This article is only describing the easier option via GROUP BY.

The Process

Creating loops is relatively easy task with only few critical steps:

  1. Get your data
  2. Group as AllRows on desired level
  3. Apply custom function OR series of nested functions over the grouped tables.
  4. Expand the results

The critical step is #3 – this is where your needs have to be translated into formulas. My need is to see which variant has the highest number of clicks in every country, so we need to sort grouped subtables by clicks on country level in descending order. The second steps is to rank the rows in the sorted tables by adding an index column. The index column will tell us which variant is #1 row with the highest number of clicks.

#1 Getting the Data

In my example, I am working with a simple table which resides in the Excel file itself. However, you can create loops over e. g. 1,000 CSV or TXT or entire Excel files in similar fashion (leave a comment below the article if you are interested to see that).

#2 Grouping Your Dataset

The first step is to group your dataset on the desired level. Since I need to find the best performing variant in every country, I group only by “Country” and select option “All Rows”. This steps creates a column of “subtables” where every subtable contains data for all the variant in that specific geo:

#3 Applying Function(s) to Subtables

I will be using 2 nested formulas to do the trick, not a custom function. (If you are interested in custom function option, learn more about custom functions here). The goal is to sort the subtables by clicks and then add an index column.

So let’s start with sorting the subtables:

I am using Table.Sort() function which takes a table as first argument and then sorting criteria as list of lists as second argument. So in my case, the column “GroupedRows” contains the subtables on country level, so I can use this column as first argument in the Table.Sort function.

Then I apply the Table.AddIndexColumn function over the already existing sorting function:

Few comments about arguments of Table.AddIndexColumn:

  1. Table – the table where you want to add the index column – in my case, it’s the Table.Sort…. piece of code = the already sorted table by clicks in the descending order.
  2. New Column Name – how the new index column should be called – in my case, the name of new column is “Rank”.
  3. Starting number – the first index number in the series
  4. Increment – increment for every subsequent row.

When adding the nested formulas, it’s always good to preview the results by closing the “Custom column window” and clicking into few random rows within the new column. The results will be shown in the bottom section of the PQ window:

#4 Expanding the Results

Now the sweetest part – expanding your subtables with all the results, cleaning up the columns and filtering results for variants which ranked as 1:

Don’t want to lose time on replicating this exercise? Download the complete sample file with the loop trick used in this article:  
DOWNLOAD NOW FOR WINDOWS

Conclusion

So there you have it, we looped through X number of subtables & easily decided which is the winning variant in every country. This whole setup would take you 30 minutes max to develop according to this article.

Obviously, I am demonstrating the loop scenario on a very simple example – it could get way more complicating than this and you may need to use a custom function to do to the “looping” step.

The sample file from this article is here.

***Tip: Have a question? Ask in the comment section below or in this Facebook group. You can also sign up for our free newsletter.***

***Do you feel like you need more help? We can help you with PPC Automation and/or with your reporting under our new project MythicalReports.com here.***

11 thoughts on “Creating Loops/Iterations in Power Query

  1. Wow, PQ is so powerful. How efficient is this technique? For example, one could also group by country, Max(clicks), merge back to the original dataset and then distinct the result.

    I also have found list.accumulate to be a very effective looping tool.

    Thank you for sharing!

    1. Hi Michael,
      I think it’s very powerful. I run similar loop technique over CSV files – e.g. pulling last X rows from each file. I have successfully run it over several thousands of files where each file had 0-1000 rows. What you are saying about clicks is also possible… Basically anything is possible, the only limits are your needs & imaginations 🙂

      I have not played with list.accumulate that much yet, so I cannot comment.

      DZ

      1. Hi DanZ,

        This line will add a one column for each column in a table (looping) using List.Accumulate. This example is not that useful. But it demonstrates PQ flexibility. List.Accumulate is my favorite function.

        = List.Accumulate(Table.ColumnNames(CT), CT , (state,current) => Table.AddColumn(state, current & “.1”, each Record.Field(_,current) * 2, Number.Type))

        Mike

        1. Yea, you’re right, this List.Accumulate looks legit. I need to start playing with it 🙂

        2. Mike,

          Can you explain this formula? What is “CT” in your example? Also, at what point would I enter this code (in the advanced editor or creating a custom column)?

  2. Hi,
    Very interesting post. I’m trying to loop over several 1000 xml files, but can’t get it to work. Any hints?
    Thx!
    Ben

    1. Hmmm, if not secret, share the file with me (mail@danzrust.cz) and I will have a look. Are e. g. 2 files working while 1000 is not? Or not even 2 files can be iterated through?

      DZ

  3. Amazing work! I just started learning how to use Power Query and it is a very powerful tool.

    I have a table with more than 400,000 rows of data. I have almost 450 Event IDs and for each event, speed was measured every 0.1 seconds. So I am trying to find the average speed every 10 consecutive points (basically finding the speed for 1 second). How can I do that using power query? I am trying to do grouping but how?

    Thank you!

    1. I think you will need to use Index column in some way. I can have a look if you send me the file to mail@danzrust.cz unless it’s super secret.

      Some pivoting/unpivoting might be needed too.

      You can just send me first 1000 rows for example.

  4. Valuable information. Fortunate me I discovered your site unintentionally, and I’m
    stunned why this accident did not happened earlier! I bookmarked
    it.

Leave a Reply

Your email address will not be published. Required fields are marked *