+ Reply to Thread
Results 1 to 12 of 12

Power query - unpivot data that has multiple headings

  1. #1
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Angry Power query - unpivot data that has multiple headings

    Hi all

    I've been given a lot of data in a format like I've created in this exampleexample.png

    I'm trying to unpivot the data with power query, so I can get the data in a standard table format, and create pivot reports from it.

    I'm struggling with how to do this with multiple rows of headings. I've tried Merging and transforming and splitting - however I just can't get my head around how to do this!

    Would really appreciate some help!

    Thanks Anna

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Power query - unpivot data that has multiple headings

    first step needs to be unmerging row 2.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power query - unpivot data that has multiple headings

    Attach a sample workbook - I can't query an image.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    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,409

    Re: Power query - unpivot data that has multiple headings

    Actually, Oeldere, PowerQuery can handle the merged cells - no problem.

    Anna - there are instructions at the top of the page explaining how to attach your sample workbook.
    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.

  5. #5
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Power query - unpivot data that has multiple headings

    Thanks all - sample attached.
    Attached Files Attached Files

  6. #6
    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,811

    Re: Power query - unpivot data that has multiple headings

    Here is my MCode
    Please Login or Register  to view this content.
    v A B C D
    1 Date Attribute.1 Attribute.2 Value
    2 4/24/2020 Employee 5 Leave type Annual
    3 4/24/2020 Employee 5 Hours 8
    4 4/27/2020 Employee 5 Leave type Annual
    5 4/27/2020 Employee 5 Hours 8
    6 4/28/2020 Employee 5 Leave type Annual
    7 4/28/2020 Employee 5 Hours 8
    8 4/29/2020 Employee 2 Leave type Annual
    9 4/29/2020 Employee 2 Hours 8
    10 4/29/2020 Employee 5 Leave type Annual
    11 4/29/2020 Employee 5 Hours 8
    12 4/30/2020 Employee 2 Leave type Annual
    13 4/30/2020 Employee 2 Hours 8
    14 4/30/2020 Employee 5 Leave type Annual
    15 4/30/2020 Employee 5 Hours 8
    16 5/1/2020 Employee 2 Leave type Annual
    17 5/1/2020 Employee 2 Hours 8
    18 5/1/2020 Employee 5 Leave type Annual
    19 5/1/2020 Employee 5 Hours 8
    20 5/4/2020 Employee 2 Leave type Annual
    21 5/4/2020 Employee 2 Hours 8
    22 5/5/2020 Employee 2 Leave type Annual
    23 5/5/2020 Employee 2 Hours 8
    24 5/6/2020 Employee 2 Leave type Annual
    25 5/6/2020 Employee 2 Hours 8
    Attached Files Attached Files
    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

  7. #7
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Power query - unpivot data that has multiple headings

    Thanks Alan

    I did manage to get to what you've done - however i'm wanting to have the leave type and hours as headings - so more like this:
    v A B C D
    1 Date Attribute.1 Leave type Hours
    2 4/24/2020 Employee 5 Annual 8
    3 4/27/2020 Employee 5 Annual 8
    4 4/27/2020 Employee 5 Annual 8
    5 4/28/2020 Employee 5 Annual 8
    6 4/28/2020 Employee 5 Annual 8
    7 4/29/2020 Employee 2 Annual 8
    8 4/29/2020 Employee 2 Annual 8

  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,811

    Re: Power query - unpivot data that has multiple headings

    Revised Mcode

    Please Login or Register  to view this content.
    Data Range
    A
    B
    C
    D
    1
    Date
    Attribute.1
    Leave Type
    Hours
    2
    4/24/2020
    Employee 5
    Annual
    8
    3
    4/27/2020
    Employee 5
    Annual
    8
    4
    4/28/2020
    Employee 5
    Annual
    8
    5
    4/29/2020
    Employee 2
    Annual
    8
    6
    4/29/2020
    Employee 5
    Annual
    8
    7
    4/30/2020
    Employee 2
    Annual
    8
    8
    4/30/2020
    Employee 5
    Annual
    8
    9
    5/1/2020
    Employee 2
    Annual
    8
    10
    5/1/2020
    Employee 5
    Annual
    8
    11
    5/4/2020
    Employee 2
    Annual
    8
    12
    5/5/2020
    Employee 2
    Annual
    8
    13
    5/6/2020
    Employee 2
    Annual
    8

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power query - unpivot data that has multiple headings

    Another option - change the filepath in the first step, as required:

    Please Login or Register  to view this content.

  10. #10
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power query - unpivot data that has multiple headings

    Or another:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-10-2016
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    16

    Re: Power query - unpivot data that has multiple headings

    Thanks guys - just what I needed! really appreciate your help!

  12. #12
    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,811

    Re: Power query - unpivot data that has multiple headings

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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: 4
    Last Post: 02-17-2020, 06:03 AM
  2. 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
  3. [SOLVED] Power Query to Extract Data based on multiple criteria
    By Philipsfn in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 01-16-2018, 01:45 PM
  4. [SOLVED] Macro to Unpivot a Flat Data with Merged headings
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2017, 11:27 AM
  5. Unpivot in Power Query keeping two columns
    By PennyK in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-10-2017, 06:27 AM
  6. Replies: 0
    Last Post: 10-25-2016, 02:59 AM
  7. [SOLVED] power query editor - unpivot?
    By pccamara in forum Excel General
    Replies: 5
    Last Post: 09-07-2016, 10:26 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