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  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  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 :
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  columns for each Expense Type:
Come back next time for more ways to use Power Query!