Power Query: Equal Split Part 5

Welcome to our Power Query blog.  Today, I continue to 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: 

In Part 3, 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.  Last time, I decided to 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 exported this data to Power Query, and now I have a parameter to use:

I now have a parameter to use:

I took a reference copy of the query I created in Part 1, Accounts, as I wish to keep the original steps:

I called this reference query AccountsNS_Split.  I also created a helper query, Helper_Split.

This time, I will tidy up my helper query and complete the solution.

I don’t need Column1, I only need the correct number of rows (given by the parameter Split_By) with values in Link.  I select Link and choose to ‘Remove other Columns’ from the right-click menu.  This gives me a table with a single column.  As I am going to merge this table using Link, I change the data type to ‘Whole Number’.  Since I will not be using this query for anything else, I choose to ‘Merge Queries’, which will merge my chosen data with this query:

In the dialog, I select AccountsNS_Split, and link using the Link column and a ‘Left Outer’ join:

This effectively gives me (in this case) three [3] copies of AccountsNS_Split in the same table.

I expand the data (not including column Link). 

This gives me the number of rows that I need, but I also need to divide Amount by Split_By.  To get the M code I need, I start by selecting Amount, and then choosing to Divide on the Standard section in the Transform tab:

In the dialog, I choose to divide by three:

This generates the M code I need:

To make this dynamic, I change the original M code from:

to:

This doesn’t affect the output, as Split_By is currently three. 

I tidy up the data by filling down the Name and Month and removing column Link.  I am not concerned about the decimal places in Amount, as I will be formatting the output in Excel when I load the data to the workbook. 

I also rename the query AccountsNS_SplitFlex.  On the Excel sheet, I change the value in cell G1 to 10:

When I refresh the query, more rows appear, and the Amount is updated appropriately:

Come back next time for more ways to use Power Query!