# Consolodating Date - Power Query

1. ## 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

2. ## Re: Consolodating Date - Power Query

``Please Login or Register  to view this content.``

3. ## Re: Consolodating Date - Power Query

``Please Login or Register  to view this content.``

4. ## Re: Consolodating Date - Power Query

Thank you both so much!

5. ## Re: Consolodating Date - Power Query

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

6. ## Re: Consolodating Date - Power Query

``Please Login or Register  to view this content.``

7. ## Re: Consolodating Date - Power Query

``Please Login or Register  to view this content.``

Or just forPivot
``Please Login or Register  to view this content.``
and use Power Pivot Measure

Total Amount
=Sum(ForPivot[AMOUNT])
Total Price
=Sumx(ForPivot,ForPivot[AMOUNT]*ForPivot[PRICE])
Average Price
=DIVIDE([Total Price],[Total Amount])

8. ## Re: Consolodating Date - Power Query

Amazing! Thank you so much

9. ## Re: Consolodating Date - Power Query

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

10. ## Re: Consolodating Date - Power Query

Delete Table or Sheet that create from Power Query.

on the Queries & connections Panel > Right click Query > Load to > Select Table2020-09-09 17_59_13-.png

There are currently 1 users browsing this thread. (0 members and 1 guests)