+ Reply to Thread
Results 1 to 10 of 10

Unpivoting using Power Query

  1. #1
    Forum Contributor TheRetroChief's Avatar
    Join Date
    09-30-2008
    Location
    UK
    MS-Off Ver
    2019
    Posts
    136

    Unpivoting using Power Query

    Hello there,

    Been a while since I've been on here. Hoping I can tap into your expertise.

    I've been given a task where I need to convert one dataset to be formatted similarly and merged with another dataset. I've almost managed to get there using the unpivoting functionality of power query - I can handle the multiple header rows, but the repeating columns are defeating me.

    I've attached sample data - essentially I need to get the data in the 'Table' sheet to look like the data in the 'Excel' sheet. I know about filling the gaps in the column A during the process. And also that Product Code, CustomerKey and Product Name will be static rows. I'm not sure how to account for the the repeating Quantity, Unit Cost and Sales Amount columns.

    I'd be very appreciative if someone could guide me through the process.

    Cheers,
    TRC
    Attached Files Attached Files
    Last edited by TheRetroChief; 10-17-2020 at 09:34 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Unpivoting using Power Query

    In your example there appears to be no correlation between the two worksheets. It would be very helpful, if your examples matched up so that it was easier to follow. Difficult to follow your example.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor TheRetroChief's Avatar
    Join Date
    09-30-2008
    Location
    UK
    MS-Off Ver
    2019
    Posts
    136

    Re: Unpivoting using Power Query

    Duplicated
    Last edited by TheRetroChief; 10-17-2020 at 11:45 AM.

  4. #4
    Forum Contributor TheRetroChief's Avatar
    Join Date
    09-30-2008
    Location
    UK
    MS-Off Ver
    2019
    Posts
    136

    Re: Unpivoting using Power Query

    Quote Originally Posted by alansidman View Post
    In your example there appears to be no correlation between the two worksheets. It would be very helpful, if your examples matched up so that it was easier to follow. Difficult to follow your example.
    Hi Alan,

    The same type of data is in both sheets; Product Code, CustomerKey, Product Name, Delivery Date, Quantity, Unit Cost, Sales Amount. Nothing more, nothing less.

    The layout in the 'Table' sheet is a nightmare. The problem is that basically the Delivery Date and Quantity/Unit Cost/Sales Amount have been spread out horizontally
    I need to get the data in there looking like the data in the 'Excel' sheet so that I can merge them. I understand that the Product Code will need to be filled down and that there may have to be some filling of dates possibly, but I think that is covered in the Power Query bit?

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Unpivoting using Power Query

    If I am understanding correctly, then this Mcode should do it for you. It is in the file. The code is too long to post
    Attached Files Attached Files
    Last edited by alansidman; 10-17-2020 at 01:01 PM.

  6. #6
    Forum Contributor TheRetroChief's Avatar
    Join Date
    09-30-2008
    Location
    UK
    MS-Off Ver
    2019
    Posts
    136

    Re: Unpivoting using Power Query

    Quote Originally Posted by alansidman View Post
    If I am understanding correctly, then this Mcode should do it for you. It is in the file. The code is too long to post
    Thanks for your effort....that's very similar to what I got to. However, I need to get the data in the 'table' sheet to be in the same format as that in the 'Excel' sheet. In those 7 specific columns.
    i.e. Your Value column needs to be split out to Quantity, Unit Cost and Sales Amount. In the sheet you returned, the first three rows should be all the same line

    Apologies if i'm not explaining it very well

    Cheers

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Unpivoting using Power Query

    There is no value column in Alan's sample workbook ... It looks exactly the same as the Excel sheet.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Unpivoting using Power Query

    I have redone the sheet. Look again at the updated sheet that I amended. It contains the three new columns. I was a little hasty in posting the first time.

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

    Re: Unpivoting using Power Query

    Please try

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

  10. #10
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Unpivoting using Power Query

    I believe what you look for is a PivotTable, not some Power Query.
    Attached Files Attached Files

+ 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. Replies: 10
    Last Post: 06-25-2020, 12:19 AM
  2. [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
  3. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  4. Excel Power Query Refresh or Access Query - 2nd Query Run is faster
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2020, 10:16 AM
  5. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  6. 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
  7. Replies: 0
    Last Post: 04-05-2018, 01:16 AM

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