Скачать или смотреть Excel - How To Unpivot Data In Excel Power Query - Episode 1909

Microsoft Excel Tutorial: Unpivot Data in Excel.

Welcome to the MrExcel podcast. In this episode, we will be discussing a classic Excel problem - how to unpivot data using Power Query. This issue has been covered on the podcast before, but the solution has never been easier thanks to Power Query.

The problem we are facing is that we have a repeating group of data going across columns instead of down. This makes it difficult to create a Pivot Table, as we need a data set with four times as many records and an extra column to identify the quarter. In the past, we have used complicated tricks to solve this issue, but now with Power Query, it's a breeze.

To get started, make sure your data is in an existing table in Excel. Then, go to the Power Query tab and choose "From Table". This will open the Power Query - Query Editor, where you will see tabs for Home, Transform, Add column, and View. Select the columns that need to be unpivoted, click on "Transform", and then choose "Unpivot Columns". Just like that, your data will be transformed into a format that is much easier to work with.

Next, we need to rename the "Attribute" column to "Qtr" for quarter. Once that is done, click on "Home" and choose "Apply&Close" to go back to Excel. Your original data will still be there, but now you have a new table with the unpivoted data. From here, you can easily create a Pivot Table and analyze your data.

Now, let's talk about Power Query. This is part of the new "Power BI" tools, which also includes Power Pivot, Power View, Power Map, and Q&A. Most of these tools are only available in Excel 2013, but Power Query is free to download for Excel 2010 and 2013. Simply search for "Download Power Query" and you can start using this amazing set of data cleansing tools.

In conclusion, Power Query is a game-changer when it comes to unpivoting data in Excel. It's easy to use and can save you a lot of time and frustration. Thank you for watching this episode of the MrExcel podcast, and be sure to tune in next time for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...


Table of Contents:
(00:00) UnPivot With PowerQuery
(00:10) The Problem: Repeating Group in Pivot Table
(00:30) Solution: Power Query
(01:03) Using Power Query to Unpivot Data
(02:34) Creating Pivot Table with Unpivoted Data
(03:02) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #powerquery #excelunpivot #pivottable

This video answers these common search terms:
how to unpivot data in excel power query
how do i unpivot a table in excel
how to unpivot columns in excel
how to unpivot data in excel
how to unpivot data on excel
how to unpivot in excel
unpivot columns in excel
excel unpivot table transformation
melting data in excel
transform wide data to long in excel
using power query to unpivot data in excel
reshaping data from wide to tall in excel
excel unpivot multiple columns

Pivot tables are great when the source data is formatting correctly. But today, our data set has the Quarter field stretching across the columns instead of down the rows. This creates an awkward pivot table. The old solutions (see episodes 1787 and 705) took a lot of manual effort. Today, I show how the Power Query tool for Excel 2010 and Excel 2013 make this incredibly easy.

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...