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:
![](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)
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:
![](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. This time, I will 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 click on cell G1, and choose to extract my data to Power Query by right-clicking and choosing to ‘Get Data from Table/Range’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1706607659.png/ab9b7d6012556e6a8e344b1c5bfa035a.jpg)
This automatically creates a new query called Split_By.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1706607677.png/3005c85c0b205ef5916a8ad893a8a727.jpg)
I click in the cell, and right-click to ‘Drill Down’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1706607695.png/a51503688cea640b8ec76d4c5fbf17bc.jpg)
I now have a parameter to use:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1706607717.png/8ca4088cf555cd1995d0a7e4f8da95ec.jpg)
I take 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 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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1706607752.png/e844396734f4ee2cb8c368e7e830f4ae.jpg)
I call the new blank query Helper_Split:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1706607777.png/196a000e46d5a3a49ca44a25ff3e1699.jpg)
The M code I have used to create the list is:
= {1..Split_By}
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1706607830.png/27acc0e95ce06018f27a5b2d15786ff5.jpg)
I take the defaults and add a new ‘Custom Column’ from the ‘Add Column’ tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1706607847.png/5b18fcf23b44c2a04daa41e36fc06b40.jpg)
I create a new column Link, which is always one [1]. This is similar to the approach I used inPart 1.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1706607866.png/43f15380ee7d1957bf35b4ce286c8150.jpg)
Next time, I will tidy up my helper query and complete the solution.
Come back next time for more ways to use Power Query!