Please note javascript is required for full website functionality.


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:

In Part 1, I created two queries:

I looked at a simple way to achieve the result I want, which assumes that I always have two suppliers:

Last time, I refined this solution to make it more flexible, by using a parameter for the number of suppliers: 

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.

I will split the rows into two [2] equal parts.  I already have the query I extracted in Part 1:

I am going to take a reference copy, because I want my new query to be updated when Accounts is updated.

I call the new query AccountsNoSupplier:

I begin by selecting Name and Month, and right-clicking to ‘Fill Down’:

This gives me my tidy query:

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:

I choose to divide by two [2]:

This gives me half the amount on each row.  Next, on the ‘Add Column’ tab, I create a ‘Duplicate Column’:

I select Amount and Amount-Copy, and on the Transform tab, I choose to ‘Unpivot Columns’:

This gives me extra rows:

I can tidy up my data, and I have the amounts split over two [2] columns for each Expense Type:

Come back next time for more ways to use Power Query!