Power Query: Equal Split Part 4

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 [2] 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 [2] 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 [1].  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!