Spread value of a row in other rows

[LoryMenCR]

Dear All,

i have the following table:

Code	Desc	Value
3	Code 3 - Desc 1	100
3	Code 3 - Desc 2	50
5	Code 5 - Desc 1	200
7	Code 7 - Desc 1	300
7	Code 7 - Desc 2	600
9	Code 9 - Desc 1	800
100	Code 100 - Desc 1	1000

I also have  a table which defines my codes:

Code	Code Name
3	Name 3
5	Name 5
7	Name 7
9	Name 9
100	Name 100

What i need is to spread the value of Code 100 throughout all the other codes proportionally.
Result would be the following:

Code	Desc	Value
3	Code 3 - Desc 1	100
3	Code 3 - Desc 2	50
3	100	250
5	Code 5 - Desc 1	200
5	100	250
7	Code 7 - Desc 1	300
7	Code 7 - Desc 2	600
7	100	250
9	Code 9 - Desc 1	800
9	100	250
100	Code 100 - Desc 1	1000

Any idea?
Thanks a lot

[nandic]

Hi @LoryMenCR ,
This makes it harder a little bit, but attached solution.
I am not Power Query expert, if there is an easier solution, it would be perfect!

Steps:
Based on main table, create additional two tables which result is 1 value.
1) 1st table: distinct count of codes (except 100)
2) 2nd table: value of code 100

When we have this, we duplicate main table (or just copy M script to prevent creating duplicate tables)
1) add new column: just reference 1st table from above and expand it
2) same thing for 2nd table
3) create 3rd column where you divide 1st expanded column with 2nd expanded column
Rename all columns to be same as main table.
And then just create append table.

Cheers,
Nemanja Andic

[LoryMenCR]

Dear @nandic I appreciate your support and thank you a lot, but i tried and won't work.
I might open a new discussion trying to put the question in a more detailed way.

Have a nice day

[v-zhengdxu-msft]

Hi @LoryMenCR 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nandic]

Hi @LoryMenCR , @RYRY sent great example of how it should be applied. 

The second table is dynamic as it is based on the main table. Just remove duplicates based on column Code and remove row for Code 100.
After that you add 2 new columns:
1. Desc = "100". It should be formatted as text
2. Value = 250. This one is the only static part of the solution.
Append will combine these two tables and it will return correct data.

If column Value should not be static (250), but have some kind of dynamic formula, just send requirement and we will try to find the solution.

Cheers,
Nemanja Andic

[LoryMenCR]

Dear @nandic , thanks a lot for your contribution. 

Actually 250 should not be static as it is the value of Desc = "100" divided by the number of distinct codes (except for code "100") which vary day by day.

I really like the approach though (don't know if this could be reach through a measure...)

[ryan_mayu]

pls see if this is what you want

1. create a new table based on the second table

then append the new table to the first tabel

pls see the attachment below

[LoryMenCR]

Dear @ryan_mayu , the execution would be great but I need an automatic input of values, not a manual one.