+ Reply to Thread
Results 1 to 10 of 10

Consolodating Date - Power Query

  1. #1
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    76

    Post 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
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,960

    Re: Consolodating Date - Power Query

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,487

    Re: Consolodating Date - Power Query

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    76

    Re: Consolodating Date - Power Query

    Thank you both so much!

  5. #5
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    76

    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. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,487

    Re: Consolodating Date - Power Query

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,960

    Re: Consolodating Date - Power Query

    Please try

    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])
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    76

    Re: Consolodating Date - Power Query

    Amazing! Thank you so much

  9. #9
    Registered User
    Join Date
    12-18-2018
    Location
    Phuket, Thailand
    MS-Off Ver
    Windows 10 - MS Office 2016
    Posts
    76

    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. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,960

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  2. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. [SOLVED] power query date format
    By orhanceliloglu in forum Excel General
    Replies: 3
    Last Post: 06-18-2018, 08:08 AM
  5. [SOLVED] Power Query URL by date
    By jespo1351 in forum Excel General
    Replies: 11
    Last Post: 05-22-2018, 01:37 PM
  6. [SOLVED] Power Query Date Transformation
    By Moggzzz in forum Excel General
    Replies: 2
    Last Post: 03-21-2018, 02:47 PM
  7. Power Query - Filter by Date
    By brent_milne in forum Excel General
    Replies: 0
    Last Post: 03-16-2017, 10:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1