Consolodating Date - Power Query

Hi guys,

I would like some help with a power query!

In at attatched workbook, there is a table, rows I-N.

I would like all the duplicate values consolidated into one. The table would merge by PID (column J), it would ADD the values in collumn K, L and M, and simple combine the names in collumns I and N.

I'd like the consolidated table to be displayed in columns P through U.

Any help is greatly appreacited.

Thank you so much,

Danny

Thank you both so much!

Hi Alan,

Once again, thank you for your great help!

There is one small but important issue with the consolidation, and again, I unfortunately don't have the knowledge to fix the issue.

The average price calculation (in collumn T) takes the average of a single purchase for all the listen products, not the total number of purchases (Amount; column K).

For example:
If you search XF7 (in the blue table) you will see 3 different values for the Chicken & Bacon Sandwich.
87 units at ฿160
10 units at ฿200
1 unit at ฿350

Your average calcuation (column T) has taken the average of the 3 selling prices, at 1 unit only (฿236.67). The calcluation needs to be the average of all of the sales, as so:
87 units at ฿160 = ฿13920
10 units at ฿200 = ฿2000
1 unit at ฿350 = ฿350
TOTAL FOR 98 UNITS IS ฿16270 - AVERAGE(PRICE PER UNIT) IS ฿166.02

Can I kindly ask for your help once more? I would like the PQ formula to calculate the average as explained.

Kindest regards,

Danny

Amazing! Thank you so much

After such helpful guidence, I have been able to create my own PQ table from scratch!! Thank you once again!

Can you tell me how to insert the resulted table onto the SAME sheet (couple of collumns to the right), not another automatically loaded sheet?

Danny