After combining 2 tables got something strange

[Antoinette123]

I have a column "Name", in which each value is repeated twice. And each value has its own share (input data). I need to calculate the average share among the first occurrence of values in the first column, and the average for the second occurrence, for each subgroup (the subgroups are distinguished by the first part of the name: like input car 1, car 1, car 2, car 2, bike 1, bike 1, bike 2, bike 2, etc (word + number). An example is shown in the figure. I already created an occurrence index.

 To solve the task, I used "group by" and most of my columns disappeared in the new table. And when I try to merge the tables, I get strange results with many rows being repeated million times. How can I do it properly in Power Query? The data is in the comment under the post.

[Anonymous · Accepted solution]

Hi @Antoinette123 

For your question, here is the method I provided:

You need an Original table and add a Copy table.

“Original table”

“Copy table”

First, the Copy table is group by.

The original and copy tables are then merged. You can choose to merge a new table.

Here is the result.

Regards,

Nono Chen

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

[Antoinette123]

Data:

Owner	Name	Share	Index	What I need ->	Owner	Name	Share	Index	Average share
Bob	Car 1	0,45	1		Bob	Car 1	0,45	1	(0,45+0,4)/2
Bob	Car 1	0,55	2		Bob	Car 1	0,55	2	(0,55+0,6)/2
Bob	Car 2	0,4	1		Bob	Car 2	0,4	1	(0,45+0,4)/2
Bob	Car 2	0,6	2		Bob	Car 2	0,6	2	(0,55+0,6)/2
Alex	Bike 1	0,3	1		Alex	Bike 1	0,3	1	(0,3+0,2)/2
Alex	Bike 1	0,7	2		Alex	Bike 1	0,7	2	(0,7+0,8)/2
Alex	Bike 2	0,2	1		Alex	Bike 2	0,2	1	(0,3+0,2)/2
Alex	Bike 2	0,8	2		Alex	Bike 2	0,8	2	(0,7+0,8)/2