Power Query: Equal Split
10 January 2024
Welcome to our Power Query blog. Today, I look at a problem where I need to divide amounts over two rows.
I know you’ve missed her: Mary, my top imaginary salesperson is back! She has some accounting data, which she needs to split equally for two suppliers:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image1-1704802596.png/9700054b2ba060d8c007c96c6c80e394.jpg)
I start by extracting the accounting data to Power Query. One way to do this, is to click anywhere in the first data set and right-click:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image2-1704802611.png/3731bae9f4ba0f09b477e26eccfa5aa5.jpg)
From the drop-down menu, I choose to ‘Get Data from Table/Range’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image3-1704802630.png/cf84d5511345bc765a144c5ba298e384.jpg)
I take the defaults and create the first query:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image4-1704802649.png/7bca00bcde46ee299a8b6b5a45e9b98a.jpg)
I rename the first query Accounts, and ‘Close & Load To’ from the Home tab to create this query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image5-1704802664.png/258ea8ec3ff73c063f0bf841a1b7da00.jpg)
I choose to ‘Only Create Connection’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image6-1704802684.png/baa0c9ff5d01ef06b8e70c1636884ca5.jpg)
I repeat the process to get the suppliers:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image7-1704802706.png/90a4595ae2a17c1a2bcec918fce64b58.jpg)
Today, I will look at a simple way to achieve the result I want, which assumes that I always have two [2] suppliers. I start by merging my data. Since there is no way to link the queries as they have no shared data, I need to create a ‘dummy’ column to link them on. I can do this from the ‘Add Column’ tab, where I choose to create a ‘Custom Column’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image8-1704802733.png/5a53b3a9edf9c5bd58df9d0e02eeadc9.jpg)
This gives me a new column called Link, which has the value 1.
I change the data type to ‘Whole Number’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image9-1704802752.png/0d525cffd0e9370f6c58a2c8dbe387dc.jpg)
I repeat the process for Accounts.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image10-1704802768.png/191652babdf24a32a3b2a722c503caf6.jpg)
Now, I can merge my data, using the ‘Merge Queries as New’ option from the ‘Merge Queries’ dropdown on the Home tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image11-1704802791.png/63bf199dd83c53faab799579eac17606.jpg)
It doesn’t matter which query I start with. Here, I am starting in Accounts:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image12-1704802816.png/6e09055da5d155c6ad8d80c4eff77447.jpg)
Having joined my queries using Link, I take the default ‘Left Outer’ join, and click ‘OK’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image13-1704802877.png/0d891b9f57eba33c403d91fd742558e7.jpg)
I expand the data in the Suppliers column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image14-1704802897.png/056432a805f7a18fcaffa5d72b8e9c41.jpg)
I only need the Suppliers column, and I have no use for the prefix.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image15-1704802915.png/030e1b91d28309b4935773dcf6a888e2.jpg)
In this simple scenario, I am assuming I only have two suppliers (I will refine this next time). Therefore, I may simply divide the Amount column by two [2], using the Standard options on the Transform Tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image16-1704802938.png/5aa96996562ce17a0f7cfbb09aeee107.jpg)
This gives me a dialog to fill out:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image17-1704802965.png/d86f30578807e8654bdb30fc692eed4c.jpg)
I now have the data I need, and I can select Name and Month and right-click to ‘Fill Down’. I also rename the supplier data column to Suppliers. Note that I could have done this by changing the ‘Expanded Suppliers’ step if I wanted to reduce the steps created. Finally, I remove the Link column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/./image18-1704802998.png/da0711f0bdc58706a8f853828a5cce85.jpg)
Next time, I will refine this solution to make it more flexible.
Come back next time for more ways to use Power Query!