topic Re: After combining 2 tables got something strange in Pipelines

After combining 2 tables got something strange

Antoinette123 — Fri, 30 Aug 2024 01:25:55 GMT

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.

Re: After combining 2 tables got something strange

Antoinette123 — Fri, 30 Aug 2024 01:19:50 GMT

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

Re: After combining 2 tables got something strange

Anonymous — Mon, 02 Sep 2024 02:10:27 GMT

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.