Power Query: Equal Split

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 [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’:

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 [2], 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!