Power Query: Equal Split Part 5
7 February 2024
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1706607520.png/4252c03a412fa166e43b8d743686d0a4.jpg)
In Part 1, I created two queries:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1706607544.png/6cfb179605cebc9c16ebb98dbb329a68.jpg)
I looked at a simple way to achieve the result I want, which assumes that I always have two [2] suppliers.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1706607563.png/8105a60af5a82d9065ec8c52434e9788.jpg)
In Part 2, I refined this solution to make it more flexible, by using a parameter for the number of suppliers:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1706607584.png/e88cd57859da9ae9fe0b8b0aecdb5113.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1706607603.png/c4003301ec5686799ab2c117ffc0c366.jpg)
I needed to split the rows into two [2] equal parts.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1706607620.png/d9db3bc73ca95712a5b3c55553ae419e.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1706607639.png/d70a810b451b93dac10628f782ee2b8d.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1706607717.png/8ca4088cf555cd1995d0a7e4f8da95ec.jpg)
I took a reference copy of the query I created in Part 1, Accounts, as I wish to keep the original steps:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1706607732.png/c231a7f7412d222201f68865932df0e0.jpg)
I called this reference query AccountsNS_Split. I also created a helper query, Helper_Split.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1706607866.png/43f15380ee7d1957bf35b4ce286c8150.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1707133733.png/5b462b886a7492bff9860f6b4a50e6b8.jpg)
In the dialog, I select AccountsNS_Split, and link using the Link column and a ‘Left Outer’ join:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1707133701.png/44388af166d1883491815e0f72e468b6.jpg)
This effectively gives me (in this case) three [3] copies of AccountsNS_Split in the same table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1707133758.png/6f1103b0d15abd75cd814bdb8c94e6b2.jpg)
I expand the data (not including column Link).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1707133775.png/9807c8ef0d29ac0e9e32e637999ce8d7.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1707133825.png/889614db1d6cb650e6ef4e4a77eb776e.jpg)
In the dialog, I choose to divide by three:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1707133852.png/b3f26ae9df534df6c46335a92caa7e9a.jpg)
This generates the M code I need:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1707133868.png/236e5563d21e26ae1b61a61020645b04.jpg)
To make this dynamic, I change the original M code from:
= Table.TransformColumns(#"Expanded AccountsNS_Split", {{"Amount", each _ / 3, type number}})
to:
= Table.TransformColumns(#"Expanded AccountsNS_Split", {{"Amount", each _ / Split_By, type number}})
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image18-1707133943.png/58b32e2ec3455f1102d3cf873807c891.jpg)
I also rename the query AccountsNS_SplitFlex. On the Excel sheet, I change the value in cell G1 to 10:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image19-1707134053.png/dce01db42add2dabedb6ebf54fa5a0f0.jpg)
When I refresh the query, more rows appear, and the Amount is updated appropriately:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image20-1707134074.png/64cb3eb6b7fbeee4911290c484827f69.jpg)
Come back next time for more ways to use Power Query!