Distribution of values based on percentages

[LorenzoC]

Dear All,

I have a table that looks like this:

Code	Group	Value	Date
1	abc	1000	15/06/2024
1	xyz	1500	30/06/2024
2	abc	2000	10/07/2024
3	xyz	3000	15/07/2024
4	abc	1000	01/06/2024
4	abc	500	02/09/2024
5	xyz	100	03/09/2024

What I want to achieve is: for certain codes I want to distribute the value based on percentages within the month.

I have another table that control percentages and it looks like this:

Start Code	Start Code Description	End Code	%
2	Fruit	1	40
2	Fruit	4	60
3	Grocery	1	20
3	Grocery	4	30
3	Grocery	5	50

This means that Code number 2 values will always be splitted throught the end codes (1 and 4) with certain percentages.

What I want to achieve through PowerQuery or Dax is a table like this:

Code	Group	Value	Date
1	abc	1000	15/06/2024
1	FRUIT	800	10/06/2024
1	xyz	1500	30/06/2024
1	GROCERY	600	15/07/2024
2	abc	2000	10/06/2024
3	xyz	3000	15/07/2024
4	abc	1000	01/06/2024
4	FRUIT	1200	10/06/2024
4	GROCERY	900	15/07/2024
4	abc	500	02/09/2024
5	GROCERY	1500	15/07/2024
5	xyz	100	03/09/2024

I think this could be achieved better through Powerquery?

Thank you a lot for your support

[ryan_mayu]

how do you get 800 for 1 FRUIT?

[LorenzoC]

It is 40% of 2000 (row 3 of table one and row 2 of table two)

[ryan_mayu]

why the date is 6/10?

[LorenzoC]

Spoiler
Yeah sorry, that is an error. Date should be 10/07/2024 for code 2. Also, code that have been distributed (in this case 2 and 3) should be removed from the table (otherwise their value would be counted two times)

[ryan_mayu · Accepted solution]

@LorenzoC 

you can merge two tables and append the result to table 1. pls see the attachment below