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:
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:
From the drop-down menu, I choose to ‘Get Data from Table/Range’:
I take the defaults and create the first query:
I rename the first query Accounts, and ‘Close & Load To’ from the Home tab to create this query.
I choose to ‘Only Create Connection’:
I repeat the process to get the suppliers:
Today, I will look at a simple way to achieve the result I want, which assumes that I always have two  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’:
This gives me a new column called Link, which has the value 1.
I change the data type to ‘Whole Number’.
I repeat the process for Accounts.
Now, I can merge my data, using the ‘Merge Queries as New’ option from the ‘Merge Queries’ dropdown on the Home tab:
It doesn’t matter which query I start with. Here, I am starting in Accounts:
Having joined my queries using Link, I take the default ‘Left Outer’ join, and click ‘OK’.
I expand the data in the Suppliers column:
I only need the Suppliers column, and I have no use for the prefix.
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 , using the Standard options on the Transform Tab:
This gives me a dialog to fill out:
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:
Next time, I will refine this solution to make it more flexible.
Come back next time for more ways to use Power Query!