Power Query: Equal Split Part 3
24 January 2024
Welcome to our Power Query blog. Today, I look at a slightly different problem where I need to divide amounts over rows.
Over the last two [2] 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-1706008311.png/d0e584d58e6a60d372e1d3dc046ab1af.jpg)
In Part 1, I created two queries:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1706008357.png/e2f2e5ab48581f54c7610cd40a344692.jpg)
I looked at a simple way to achieve the result I want, which assumes that I always have two suppliers:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1706008451.png/d9d760acc752ed8641fd1a4650d3a03d.jpg)
Last time, 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-1706008509.png/b8014a8d5a92df3e22af00b821492250.jpg)
This time, I am going to look at a slightly difference scenario. I still begin with the same accounting data, but this time, there are no suppliers to link to.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1706008540.png/91f7ceb253e3afc24aa6cc7b65a3ec0f.jpg)
I will split the rows into two [2] equal parts. I already have the query I extracted in Part 1:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1706008568.png/b8efe519eb0c2b319f1ef250a07b67ae.jpg)
I am going to take a reference copy, because I want my new query to be updated when Accounts is updated.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1706008588.png/3aaae7c44c55dcdee763141dabf27b01.jpg)
I call the new query AccountsNoSupplier:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1706008610.png/43fd7caeba6819cf5b74fb3f811649d7.jpg)
I begin by selecting Name and Month, and right-clicking to ‘Fill Down’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1706008635.png/ca9c97e51321ab40101243547bbce3f2.jpg)
This gives me my tidy query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1706008654.png/f8e6cc2d2a2a2f976f3a8d4bdf1d6ca5.jpg)
There is no other table to link to duplicate the rows. Instead, I will use another method. As I know I will have two rows for this example, I start in the Transform tab, where I divide the Amount column using the Standard dropdown:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1706008684.png/93a6552dee2e8ee9689ff47a9a692ebc.jpg)
I choose to divide by two [2]:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1706008708.png/604aace990648af0eae45ca605a46657.jpg)
This gives me half the amount on each row. Next, on the ‘Add Column’ tab, I create a ‘Duplicate Column’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1706008733.png/5d4a5ad13903cdf1f440fa747d9acaa4.jpg)
I select Amount and Amount-Copy, and on the Transform tab, I choose to ‘Unpivot Columns’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1706008756.png/839031af791200f7b5f65223d3eeb5cc.jpg)
This gives me extra rows:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1706008781.png/5ca6ac61ff2a7445c74705cc8367776f.jpg)
I can tidy up my data, and I have the amounts split over two [2] columns for each Expense Type:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1706008801.png/5f641b37bc828558bb5c0e9e4d954902.jpg)
Come back next time for more ways to use Power Query!