Power Query: Equal Split Part 2

Welcome to our Power Query blog.  Today, I refine a solution to a problem where I need to divide amounts over 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:

Last time, I created two [2] queries:

I looked at a simple way to achieve the result I want, which assumes that I always have two suppliers.  I created a Custom Column with a value of one [1], which I called Link, on both queries and linked them together:

Since I assumed I only had two suppliers (I will refine this today), I simply divided the Amount column by two [2], and tidied my data:

This time, I will refine this solution to make it more flexible.  Instead of assuming I have two [2] suppliers, I will calculate the number of suppliers.  I could do this with M code, but I will show a solution that uses the User Interface (UI).  I start by taking a reference copy of the Suppliers query.  I am not going to need to alter the original steps and I’d like my new query to reflect the current contents ofSuppliers, therefore a reference query is appropriate here. 

My new query only has a ‘Source’ step  which points to Suppliers.

I am simply going to group my data to get the total number of rows, using the ‘Group By’ button on the Home tab.  The default settings would allow me to count the rows for each supplier:

I change Suppliers to Link and click OK.

I can select the cell in the Count column, right-click and choose to ‘Drill Down’:

This gives me the number of suppliers, and I rename the query accordingly:

I take a copy of the SplitAccounts query I created last week.  This time, I do want to change the steps, so I can take a duplicate.  Note that if this were a real scenario where I needed both queries, I would put the earlier steps in a reference query.  Here, I am showing a difference in methods.  Therefore, a duplicate of the whole query is sufficient. 

In the duplicate query, I look at the ‘Divided Column’ step:

The M code for this step is currently:

where ‘each _/2’ is hard-coding 2 as the number of suppliers.  I want to use Number_of_Suppliers instead:

This has no impact on the data at this point:

I rename this query SplitAccountsFlex and add a supplier to Mary’s data.  When I refresh the queries and go back to SplitAccounts, the supplier has been added, but the amounts have not been updated:

However, SplitAccountsFlex shows the new distribution:

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