Hi,
I am trying to create a spreadsheet that automatically splits data and creates new rows with information.
| Origin Client | Destination Client | Original Qty | Quantity | Unit | Product |
| WFC ( Grain Plant ) | Woodslea Downs Limited | 15 | 15.18 | MT | 50:50 CB:PKE |
I want the new sheet to return the following based on the ratio in the product categories.
| Origin Client | Destination Client | Original Qty | Quantity | Unit | Product |
| WFC ( Grain Plant ) | Woodslea Downs Limited | 7.5 | 7.59 | MT | CB |
| WFC ( Grain Plant ) | Woodslea Downs Limited | 7.5 | 7.59 | MT | PKE |
Hi Sarah,
Please see example attached.
Mynda
Thanks so much Mynda,
This answers my question well, although I will now have to try and understand what you have done 😉
One more thing though. My product could be a variant or options, some with no ratio.
For e.g. of a list of products
50:50 CG:PKE
70:30 CG:PKE
60:30:10 CG:PKE:DDG
Whole Wheat
Fertiliser
How do I apply this answer but also make it assume 100% for product with no ratio?
Thanks
Sarah
ps I am new to Power Query but LOVING this course & this site. Thank you
Me again,
Having now looked at your solution I see my question needed to be more specific in the first instance as your response works for 50:50 splits only (as requested - mea culpa). See attached file with examples of split ratios, including the 100% WW showing. I can manage the source data somewhat on this to say what is needed to work in this file. For e.g. if needs be we could call it 100 WW.
A second split type is also required on this Query.
In English - some clients split their bills with their business partner and we must invoice both for their portion. The query would be if client name equals X then duplicate rows, show Trading Name on new rows as Client Y, and further split the quantities to a ratio charge per client.
The no. of split clients and their business partner is known, i.e. X and Y relationship. The ratio of split is also known and is static in the relationship, but there are possibly ten combinations.
| Client | Client | Payment ratio |
| Woodslea Downs Limited | Client Y | 50:50 |
| Client A | Client B | 79:21 |
To re use original example the end result would look like this, assuming client "Woodslea Downs Limited" named is Client X.
| Origin Client | Destination Client | Original Qty | Quantity | Unit | Product.2 |
| WFC ( Grain Plant ) | Woodslea Downs Limited | 3.795 | MT | CB | |
| WFC ( Grain Plant ) | Woodslea Downs Limited | 3.795 | MT | PKE | |
| WFC ( Grain Plant ) | Client Y | 3.795 | MT | CB | |
| WFC ( Grain Plant ) | Client Y | 3.795 | MT | PKE |
I have attached an excel sheet that better shows more variety in data on import.
Thanks
Sarah
Hi Sarah,
Thanks for the updated file. It's always best to cover all scenarios otherwise we're both wasting our time.
In the attached file I've allocated the quantity columns based on the product info.
In your other scenario where some clients split their bill with their business partner, this will need to be a separate query with separate source data. It will work similar to this one, so I think you can have a go at that yourself.
I hope that points you in the right direction.
Mynda
Thanks so much 🙂
