• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query Unpivot Scenarios

You are here: Home / Power Query / Power Query Unpivot Scenarios
July 20, 2017 by Mynda Treacy

In this post I’m going to show you how to use Power Query’s Unpivot tool to fix 3 common data layout problems:

  1. The Straightforward Unpivot
  2. Power Query Unpivot Scenarios - straightforward unpivot

  3. Unpivot Multi-column Data Types
  4. Power Query Unpivot Scenarios - unpivot multi-column data types

  5. Unpivot Nested Column Headers (the worst offender):
  6. Power Query Unpivot Scenarios - unpivot nested column headers

  7. And to flip things around, literally, we’ll use the Pivot tool to fix Repeating Rows, which is commonly referred to as Stacked Data.
  8. Power Query Unpivot Scenarios - stacked data

 

While all those examples are amazing, the icing on the top is that the query retains a link to the original data (blue tables), so should any of it change, you can simply refresh the query and the green tables get updated too.

Watch the Video

Subscribe YouTube

 

Download the Workbook

Enter your email address below to download the sample workbook containing step by step written instructions:

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

excel workbook with step by step instructions

Figure above - Excel workbook with step by step instructions

Power Query Unpivot Scenarios - Written Instructions:

Straightforward Unpivot

In this example we want to unpivot columns B:D and remove the Grand Total, in column E.

straightforward unpivot

Tip: Format the data in an Excel Table first and give it a useful name.

  1. Load the data into Power Query: Excel 2010 & 2013 Power Query tab/Excel 2016 - Data tab > From Table. This will open the Query Editor window.
  2. Remove the Grand Total column: Left click to select it and press the Delete key, or go to the Home tab > Remove Columns, as shown below:
  3. remove the grand total

  4. Unpivot the year columns: select the Salesperson column > right-click > Unpivot Other Columns:
  5. unpivot the year columns

  6. Rename Columns: double click the Attribute column header and enter a new name; Year:
  7. rename columns

  8. Change Type for Year column: Click the ABC icon in the Year column header > select 'Whole Number':
  9. change type for year column

  10. Close & Load: now you're ready to load the data back into the Excel worksheet. Click the 'Close & Load' button on the Home tab:
  11. close and load

Tip: Click the drop down arrow on the Close & Load button and choose ‘Close & Load To’ for more options on where to load the data.

Voila! Now you can create your PivotTable reports using data correctly formatted in a Tabular layout:

data in a tabular layout

Multi-column Data Types

In this example we want to unpivot columns G &H (hours) and I & J (cost) separately.

multi-column data types

  1. Load the data: Excel 2010 & 2013 Power Query tab/Excel 2016 - Data tab > From Table. This will open the Query Editor window.
  2. Unpivot the Values Columns: select the 4 columns containing values > right-click the column header > Unpivot Columns:
  3. unpivot the values column

  4. Split Attribute Column by Number of Characters: Select the Attribute column > Home tab > Split Column > by Number of Characters.

    In the dialog box enter 3 (this will split after the month name) > Split; Once, as far left as possible:

  5. split attribute column by number of characters

  6. Pivot the Attribute.2 and Value Columns: This will put the Hours and Costs values into separate columns.

    Tip: Select the Attribute.2 column first, then select the Value column > Transform tab > Pivot:

  7. pivot the attribute.2 and value columns

  8. Rename Columns: double click the column headers and rename as required:
  9. rename the header columns

  10. Close & Load: now you're ready to load the data back into the Excel worksheet or data model etc.

Nested Column Headers

In this example we want to unpivot columns G to J, but the headers are split over rows 1 and 2.

rename the header columns

  1. Format in Excel Table: select the data including the headers > CTRL+T to format as an Excel Table. Uncheck 'My Table has headers'.
  2. Load the data: Excel 2010 & 2013 Power Query tab/Excel 2016 - Data tab > From Table. This will open the Query Editor window.
  3. Transpose Table: Transform tab > Transpose:
  4. transform then transpose

    This enables us to join the two header rows together because transposing them will put them into columns, which can be joined, but first…

  5. Fill Down Month Labels: select Column1 > Transform tab > Fill Down. This will repeat the month names on each row where relevant:
  6. fill down month labels

  7. Merge Columns: merge columns 1 and 2. I won't use a delimiter because the month values in Column1 are all 3 characters long, so I can easily split the column by length later.
  8. merge columns 1 and 2

  9. Transpose: transpose the table back to its original layout:
  10. transpose the table back

  11. Promote First Row to Headers: now that the column labels are in one row we can promote them to the header. Transform tab > Use First Row as Headers:
  12. promote first row to headers

  13. Unpivot the Values Columns: select the 4 columns containing values > right-click the column header > Unpivot:
  14. unpivot the values column

  15. Split Attribute Column by Number of Characters: select the Attribute > Home tab > Split Column > by Number of Characters.
  16. In the dialog box enter 3 (this will split after the month name) > Split; Once, as far left as possible:

    split attribute column by number of characters

  17. Pivot the Attribute.2 and Value Columns: This will put the Hours and Costs values into separate columns.
  18. Tip: Select the Attribute.2 column first, then select the Value column > Transform tab > Pivot:

    pivot the Attribute.2 and value columns

  19. Rename Columns: Double click the column headers and rename as required:
  20. rename columns

  21. Close & Load: now you're ready to load the data back into the Excel worksheet or data model etc.

Repeating Rows

The data in columns B and C follows a pattern of repeating rows. We can use the Pivot tool to help convert it to a tabular layout.

repeating rows in columns B and C

  1. Load the data: Excel 2010 & 2013 Power Query tab/Excel 2016 - Data tab > From Table. This will open the Query Editor window.
  2. Remove rows we don't need: Select the Entitlements column > Filter > uncheck ‘(null)' and 'Total':
  3. remove rows we don't need

  4. Add an Index Column: Add Column > Index Column; From 0 or From 1, it doesn't really matter:
  5. add an index column

    Tip: The reason we add an index column is so that when we Pivot the data in the next step, we don't get the very unhelpful “Expression Error: There were too many elements in the enumeration to complete the operation”. This error can be triggered by duplicate values in the column being pivoted.

    Expression Error

  6. Pivot the Value column: select the Entitlements column > Transform tab > Pivot Column:
  7. pivot the value column

    In the Pivot Column dialog box under 'Advanced Options' select 'Don't Aggregate':

    aggregate value function

  8. Fill Up: select the D.O.B. through to Holiday Leave Accrual columns > Transform tab > Fill Up:
  9. transform then fill up

  10. Filter Name Column: remove the null rows. This will remove all the rows that we don't need, leaving us with just one row per person.
  11. remove the null rows

  12. Delete Index Column: we don't need the Index column anymore. Select the headers and press the Delete key.
  13. Close & Load: now you're ready to load the data back into the Excel worksheet or data model etc.

More Power Query

I bet you can’t wait to use Power Query!

Firstly, you may need to install the Power Query add-in. Click here to see which versions of Excel support Power Query and where to download and install it.

Then read more Power Query tutorials.

And if you want to get up to speed quickly, please check out my Power Query course.

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:Symbols in Excel Slicers
Next Post:Excel Convert Dates to Fiscal Quarters and Years

Reader Interactions

Comments

  1. Steve

    August 8, 2022 at 5:50 pm

    Thank you for this unpivot discussion! I’m working with a table that have a series of columns titled with Category*Year as the title with a value in the cell and wondered if you might point me in the right direction. I am trying to get this info into tabular form. Ideally, I could convert this into Date, Category and Value columns. There are four different Category Names along with up to 9 years with each category. I was trialing unpivoting each of the 4 categories of columns but found that the number of rows is expanding quickly with each successive unpivot. There are hundreds of rows in my table to begin with. Do you have any recommendations? Do I need to add an index row/column so that different dates and categories can be grouped into their respective columns (as opposed to a unique column for each which is what occurs when unpivot is done serially)? Alternatively, I was reading about List.zip and wondered if that might be applicable here?

    Reply
    • Mynda Treacy

      August 8, 2022 at 6:24 pm

      Hi Steve,

      It’s difficult to picture your data. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
    • Steve

      August 9, 2022 at 11:07 am

      Please disregard this question. I realized that I was over complicating the solution. Since there is only one value in each cell, I realized that one can unpivot any number of columns and and then parse the categoryxyear into separate columns after the data is unpivoted (as opposed to reversing these two steps). Your material is very helpful in any case! Best Regards

      Reply
  2. A

    May 28, 2022 at 1:38 am

    you are the most flipping amazing content creator, probably one of the best I’ve seen on youtube. And god damn it if it’s a compliment. Thank you for your help!

    Reply
    • Mynda Treacy

      May 28, 2022 at 11:07 am

      Wow, that’s incredibly kind of you! Thanks for taking the time to leave your comments. I’m so pleased you found my videos helpful.

      Reply
  3. Deniz Cagdas

    May 16, 2022 at 12:57 pm

    How could I do a sunburst table with excel?
    Thanks

    Reply
    • Mynda Treacy

      May 16, 2022 at 1:46 pm

      Please see this tutorial for Sunburst charts.

      Reply
  4. Janet

    July 27, 2021 at 6:09 am

    Hi –

    The file download for the Power Query Unpivot Scenarios does not work. When I click on the link to nothing happens; it does download.

    Best Regards,
    Janet

    Reply
    • Philip Treacy

      July 27, 2021 at 9:30 am

      Hi Janet,

      the download link works fine for me. Perhaps your browser or anti-malware is blocking the link. Are you getting any notifications in your browser? Try a different browser.

      Regards

      Phil

      Reply
  5. NS

    June 13, 2021 at 1:34 am

    Power Query Unpivot Scenarios – Written Instructions:
    I do not find the link for the excel file

    Reply
    • Mynda Treacy

      June 13, 2021 at 9:26 am

      The link is under the video. See heading ‘Download the Workbook’ and follow the instructions. Let me know if you’re still stuck.

      Reply
      • OZ

        July 11, 2021 at 8:39 pm

        The download doesn’t work despite trying several times.

        Reply
        • Philip Treacy

          July 12, 2021 at 6:26 am

          Hi OZ,

          How exactly does it ‘not work’? It works fine for me. If you enter your email address and click the Get Workbook button, I can download an Excel spreadsheet from the link that is revealed.

          This does require JavaScript to be enabled in your browser. Perhaps you have it disabled?

          Regards

          Phil

          Reply
  6. Steven Alker

    April 22, 2021 at 12:28 am

    Mynda: Absolutely stunning. This was the last of your free offerings I wanted to complete as I start your first two formal courses – boy am I looking forward to those.

    Steve

    Reply
    • Mynda Treacy

      April 23, 2021 at 9:10 am

      So pleased it will be helpful to you, Steve!

      Reply
  7. Jose Lobo

    March 18, 2021 at 8:47 am

    https://drive.google.com/file/d/1Snx_MqEOlRIkj1Yqjw1QsMk6bYP4xcow/view?usp=sharing

    I uploaded a .PDF file in the above link. I would like some help to transform (UNPIVOT) the table on the uploaded file in a database, with the fields:
    YEAR MONTH VALUE
    I read the help in (https://www.myonlinetraininghub.com/power-query-unpivot-scenarios) but I didn’t find out a similar model.
    If someone could give a hand I would be very grateful (from Brazil).

    Reply
    • Mynda Treacy

      March 18, 2021 at 9:50 am

      Hi Jose,

      Please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum and also share a file with our solution.

      Thanks,

      Mynda

      Reply
  8. Adeo Turatsinze

    February 8, 2021 at 5:01 am

    Thank you so much.

    Reply
    • Mynda Treacy

      February 8, 2021 at 8:56 am

      Our pleasure, Adeo!

      Reply
  9. Bhavya

    June 18, 2020 at 2:52 pm

    Hi,
    Loved your tutorial.
    Is there any way to retain the original table, so that the visualizations crated from my original table remain intact? And table created after using “unpivot selected columns” is created as a separate table?

    Reply
    • Mynda Treacy

      June 18, 2020 at 3:34 pm

      The original table isn’t altered. Power Query loads the data into a new table.

      Reply
  10. jim

    May 17, 2019 at 3:13 am

    That is amazing!

    I was familiar with the straightforward unpivot but when I had two different attributes (Value and Profit), I thought there was no easy way to proceed – and so did the web, with only the straightforward option appearing in my searches…

    …until I came here – and I couldn’t believe how easy it was (though I suspect some form of sorcery is involved)

    thanks Mynda for demonstrating so clearly

    jim (now happily moved to a company with 365)

    Reply
    • Mynda Treacy

      May 17, 2019 at 8:29 am

      Glad I could help, Jim 🙂

      I imagine you’re very pleased to now have Office 365.

      Reply
  11. Arthur Melo

    March 22, 2019 at 10:26 pm

    Hi, really nice post.

    I am having a problem to pivot the columns. I am having this error:

    DataFormat.Error: Valor de célula inválido ‘#N/A’.

    Any ideia what can be ?

    Reply
    • Catalin Bombea

      March 23, 2019 at 4:07 am

      Hi Arthur,
      One of your cells from the source data has a formula that returns an error. Clear that error in the source.

      Reply
  12. Cheri

    February 14, 2019 at 2:43 am

    Thank you for the tutorial!

    So I have several columns of revenue by month and several columns of unit sales by month. I’m not understanding how to unpivot both the revenue and the unit sales without them both ending up in the same column, rather than having a revenue column and a units column when I’m done.

    Thanks!

    Reply
    • Mynda Treacy

      February 14, 2019 at 9:20 am

      Hi Cheri,

      Please post your question and sample Excel file in our Power Query forum where we can help you with a detailed response.

      Thanks,

      Mynda

      Reply
  13. Matt

    November 3, 2018 at 8:01 am

    Thank you for the tutorial! Is there any way that I can apply this query to other sheets in the work book that are all formatted exactly the same?

    Thanks!

    Reply
    • Mynda Treacy

      November 3, 2018 at 2:59 pm

      Hi Matt,

      Yes, you can copy the query and then edit it via the Advanced Editor to point at the different sheets.

      Mynda

      Reply
  14. stephen

    November 3, 2018 at 2:58 am

    Thanks for this. Assuming I have unpivoted my data and am using the table now. If I add in some columns to the source data table, how do i include these in the unpivoted data table now? I cant figure out to do this using the edit.

    Thanks!

    Reply
    • Mynda Treacy

      November 3, 2018 at 3:04 pm

      Hi Stephen,

      If you selected the columns you wanted to keep and then ‘unpivoted other columns’, you could simply refresh your query and it will pick up the new data.

      Mynda

      Reply
  15. Clara

    October 18, 2018 at 3:07 am

    Thank you! You have no idea how much you saved me 😀

    Reply
    • Mynda Treacy

      October 18, 2018 at 7:53 am

      🙂 you’re welcome, Clara. Glad Power Query was a big help.

      Reply
  16. Carlos

    September 13, 2018 at 1:58 pm

    Hi, I would like to know more about pivot and unpivot columns and how does it work the INDEX when we got several columns.

    greetings!

    Reply
    • Mynda Treacy

      September 13, 2018 at 3:21 pm

      Hi Carlos,

      The reason we add an index column is so that when we Pivot the data, we don’t get the “Expression Error: There were too many elements in the enumeration to complete the operation”. This error can be triggered by duplicate values in the column being pivoted.

      Mynda

      Reply
  17. Uche Uche

    October 17, 2017 at 12:49 am

    Your article came to me as a coincident. I saw a tutorial on the same subject on power query but very confusing as there is no example on how to apply it, wow, it was very painful as I have developed interest in that subject.

    So when suddenly I saw your article complete with the hows, I have nothing but to thank you very much from the bottom of my heart.
    Big Thanks again

    Reply
    • Mynda Treacy

      October 17, 2017 at 8:38 am

      Hi Uche,

      Glad you found this post answered your question. Have fun with Power Query, it’s an amazing tool.

      Mynda

      Reply
  18. Mehdi HAMMADI

    July 23, 2017 at 9:40 pm

    Tahnk you Mynda for sharing, and as always, it is a real pleasure to follow your tutorials

    Reply
    • Mynda Treacy

      July 24, 2017 at 8:46 am

      Thanks, Mehdi. Glad you enjoyed it 🙂

      Reply
  19. Juan

    July 21, 2017 at 1:18 am

    I don’t have enough words to say how interesting and original is this tutorial, wow, I am so impressed with all that can be achieved with the adequate knowledge, excellent work Mynda!! I feel so identified with the figure 2 “original data” because that’s the way we usually organize time series of data in my office. I am sure this tutorial will create a revolution between my coworkers, I will forward it to my colleagues right now. As you said, I can’t wait to install Power Query, it’s a well hidden diamond!!

    Reply
    • Juan

      July 21, 2017 at 1:23 am

      You were truly the Angel for that man, he will never forget your help and you gained a loyal friend forever! 🙂 You made his day! It was a pleasure to read this tutorial, your comments were very funny. I smiled a lot while reading it :):)

      Reply
      • Mynda Treacy

        July 21, 2017 at 9:13 am

        Thank you, Juan! I’m so pleased you found the unpivot tutorial helpful and enjoyed my story (shared in my Newsletter) 🙂

        Reply
  20. Joan Hauff

    July 21, 2017 at 12:45 am

    Thank you for this very informative tutorial.

    Reply
    • Mynda Treacy

      July 21, 2017 at 9:13 am

      Thanks, Joan! Enjoy Power Query Unpivot 🙂

      Reply
  21. Andy

    July 20, 2017 at 9:19 pm

    Fantastically helpful as ever. I used to think pivot tables were the business until I came across unpivoting!
    However, my personal disaster area is unpivoting other people’s multi-column tables where the people have put all the different months or locations on different worksheets. Is there a snappy way to do unpivot those? (preferably with Excel 2010)

    Reply
    • Mynda Treacy

      July 20, 2017 at 9:38 pm

      Great to hear you’re enjoying Power Query. Of course it can consolidate data spread over multiple sheets and workbooks. There is an example here: Combine Excel worksheets using Power Query

      Hope that helps.

      Mynda

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.