Power Query: Equal Split Part 4
31 January 2024
Welcome to our Power Query blog. Today, I modify the solution to divide amounts over rows by using a parameter.
In this mini-series of blogs, I have been looking at an issue that Mary, my top imaginary salesperson had. She had some accounting data, which she needed to split equally for two suppliers:
In Part 1, I created two queries:
I looked at a simple way to achieve the result I want, which assumes that I always have two  suppliers.
In Part 2, I refined this solution to make it more flexible, by using a parameter for the number of suppliers:
Last time, I looked at a slightly difference scenario. I had the same accounting data, but this time, there were no suppliers to link to:
I needed to split the rows into two  equal parts.
However, it is often useful to make the solution more flexible. This time, I will use a parameter to decide how many ways I need to split the amounts.
I have given cell G1, containing the number to split by, a defined name, Split_By. I click on cell G1, and choose to extract my data to Power Query by right-clicking and choosing to ‘Get Data from Table/Range’:
This automatically creates a new query called Split_By.
I click in the cell, and right-click to ‘Drill Down’:
I now have a parameter to use:
I take a reference copy of the query I created in Part 1, Accounts, as I wish to keep the original steps:
I call my new query AccountsNS_Split. I need to divide the data in a different way, since I can’t just use duplicate columns as I did last week. I am going to create a helper query.
I call the new blank query Helper_Split:
The M code I have used to create the list is:
This creates a list, where the number of rows is the number in Split_By. Using the ‘List Tools’ Transform tab, I convert the list into a table, as I wish to add a column:
I take the defaults and add a new ‘Custom Column’ from the ‘Add Column’ tab:
I create a new column Link, which is always one . This is similar to the approach I used inPart 1.
Next time, I will tidy up my helper query and complete the solution.
Come back next time for more ways to use Power Query!