trying to copy value into the same table but on differents rows based on id

[nilo0123]

Hi all,

I will try to be clear and by advance sorry for my english i'm french 🙂

I use power query editor because i work to set a dataset model.

I have a table with a lots of rows

For some type of rows i put calculate columns (working well)

For those rows I have a MAIN ID (reference to an another row contains into the same table

for all the rows I have a unique ID

I try to find a solution to "copy" the value of the calculate column into a new column for the main id

what i have

ID	MAIN ID	number	type	CALCULATE column (amount)
1	0	123456	contract	0
2	1	123456/av1	addmendment	2
3	1	123456/av2	addmendment	4

What I want

ID	MAIN ID	type	CALCULATE column (amount)
1	0	contract	6
2	1	addmendment	2
3	1	addmendment	4

After success to copy I will group by my result by main ID contract and it should be good

Thanx a lot for your help

and again sorry if i made some typo errors

best regards

[v-xulin-mstf]

Hi @nilo0123,

Is your issue solved?

If the issue has been solved, please share the solution and adopt it to help others.

Thanks! 😉

Best Regards,
Link

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

[v-xulin-mstf]

Hi @nilo0123

Sorry, i'm not clear about your expected output.

Could you provide more details?

Best Regards,
Link

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

[HotChilli]

How about this..

Duplicate the table.

Remove all columns except "MAIN ID" and the calc column.

Filter out rows where "MAIN ID" is zero.

Do a 'Group By' on "MAIN ID" and sum the calc column.

This will give you a 2 column table (let's call it MainTotals) with "MAIN ID" and total.

--------------

From the original table, do a Merge Queries (as New) on the original table and MainTotals , using Left Join on ID from the first table and "MAIN ID" from MainTotals.

You can then expand new column and you have the totals from the subitems on the same row as the 'primary' items.

You can total the new column and calc column as required.

Good luck