Power Query Unpivot - fix 4 common data layouts (incl. workbook)

How to use Excel Power Query Unpivot to fix 4 common data layouts, so your data is in the correct tabular layout to work with functions and PivotTables the way they were designed.

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

💬 Please leave me a COMMENT. I read them all!

🎁 SHARE this video and spread the Excel love.

Or if you’re short of time, please click the 👍

[ronsss]

Thanks
Power Query Unpivot - fix 4 common data layouts 2017 06 05
00:00 intro
00:40 – Ideal format, Tabular
00:57 – Step by step instructions in downloaded example file
01:05 – Sales info by person by year with row totals
01:40 – How to start PowerQuery
02:00 – PowerQuery Editor Window
02:50 – Unpivot the year columns
03:49 – Rename Columns
04:05 – Change data types
04:25 – Rename Query
04:32 – Close and Load
04:48 – Refresh query output table
06:13 – Example 2: Partial tabular, repeating columns monthly hours and costs
06:42 – load to PowerQuery
07:00 – Unpivot the “values” columns
07:28 – Split month out to separate column: Split Column feature
08:02 – Pivot the Attribut column to separate Hours and Costs
08:39 – Close and Load to Excel
09:13 – Example 3: column headings over 2 rows
09:45 – Format data as table
10:18 – Load to PowerQuery
10:21 – Merge 2 header rows into 1, Transpose data
10:43 – Merge the first columns
10:50 – Fill down month values
11:04 – Perform merge: Transform tab > Text Columns group > Merge Columns command
12:05 – Trim leading spaces
12:10 – transpose back
12:14 – Promote first row to become header
12:27 – Unpivot
12:48 – Split month out of attribute column
12:57 – Trim unwanted space at front of column/
13:03 – Pivot the attribute column to split Account hours and account costs
13:16 – Rename columns
13:20 – Close and Load
13:50 – Example 4: Stacks of repeating rows
14:25 – Convert data to table
14:38 – Load to PowerQuery
14:44 – Filter to delete blank rows and total rows
15:14 – Add index column for unique numbers
15:33 – Pivot Column don’t aggregate
15:56 – Values now in staggered stacks with null values between rows
16:19 – Fill up: Transform tab > Table group > Fill up button
16:41 – review result, filled rows
17:00 – Delete duplicate rows with null values
17:09 – Remove Index column
17:16 – format date column as date only
17:44 – close and load
.
Keep up the good work.

[jasonjackson]

This video almost made me cry. This is going to be a massive time saver.

[ethofmeyr]

I watched this video 5 years ago and today it helped me with a task. These videos never lose their relevance. Thank you Mynda

[flaviomelo]

This was the biggest amounts of "AHA's" I've said in ages! Amazing tutorial!

[serdip]

Thanks for another extremely informative and practical Excel mastery video. I started teaching myself Power Query only a few months ago, after over 27 years as an Excel developer. I can honestly say that there's no turning back. Power Query will forever change how I interact with my favorite spreadsheet program.

Thank you for explaining each of these common scenarios in concise and clear terms. Everything makes sense as you presented it. I think for me, the main challenge is to develop the insight and intuition to know which Power Query menu options to use and in what sequence.

It is not always immediately obvious to me when to pivot/unpivot/transpose - sometimes repeatedly - to rearrange the data into a final tabular format ready for analysis. I've done dozens of Power Query exercises on datasets just like the ones you covered in the lecture. I guess I will get better at it with continued practice and patience.

Sometimes I end up writing complex but effective custom reusable M Code functions to arrive at the desired result, only to find out later that it could have been accomplished strictly through the UI! 🙂

Thank you!

[jared_musicSTC]

this just blew my mind. I've solved so many of these problems by manually copy and pasting data over the years! 🤯

[ajzambranop]

Dear Mynda, You must be awarded the Accounting Data Cleaning Nobel Prize for this video at the very least! Thanks! :-)

[felipe]

These are perfect examples to solve many of the every day's issues when managing data models!!!

Thank you Mynda!

[jirinakotkova]

Thank you a lot. This is going to speed up my work for sure. I really didn't know there was such an option like unpivot! My jaws literally dropped!

[michaelpammit]

Mynda, thank you for this video! You've changed my life.

Over this weekend, I had to pull data from over 400 bi-weekly print-formatted timecards made in excel. They each had 2 non-formatted tables of weeks, and separate rows for each department worked in (thankfully, column & row numbers were consistent for all of them). Using a combination of your pivot scenarios plus your "multiple files containing multiple sheets" video, I was able to get the data from ALL of the timecards in tabular format (one row per dept-date-hours) in just about 8 hours' work! And I can just refresh the query as new timecards are added! Not to mention all the error-catching utility.

Thank you!!!

[learnitinstructor]

Incredible! I've been training PowerPivot, PowerQuery, PivotTables for a very long time and didn't know there were such amazing tools to fix these data issues! Wow! Thank you so much!

[raghavan]

I work as a Power BI developer and I'm a fresher. Today I encountered a similar problem at my work and BAM! You're video just saved me so many hours of research! Thank you so much

[nkfore]

One of the best PQ videos I've seen on here and easy to follow. THANK YOU!

[jamiebell]

Thank you so much. I've recently started an online business that involves Microsoft Excel Power Query and your video was definitely an inspiration for that. Awesome teacher. I'd recommend you any day.

[bernieofalia]

Thanks for this... I was stuck for about a month in trying to solve the reapeted rows thing. your solution is AWESOME!

[matttownsend]

Two thumbs up from me. I've been working with Excel since the start, and did in fact have to hand massage lots of data sets manually or with the complex formulae you mention. If Power Query had been available then I would have saved months of work.

[nishpreet]

I am an excel enthusiast and was soooo excited watching this video...I absolutely loved it... learned something so complex in such understanding and well explained tutorial... Thank you so much...

[aphastus]

First of all, it’s incredible how it looks like every problem has a solution with this method, how someone thought of all the options. Second, you’re a godsent, Miss, you’re my favorite Excel and data youtuber. I just sent a comment in another video about me having a problem with a very messy table and YT recommended me this video (I haven’t had seen yet) in my TL. This made me realize I have to keep practicing, there’s a lot to learn!

[maggiepeng]

this video really help. I work with Excel everyday and am slowly transitioning to power query to make things more automatic. thank you !!

[sjdaa]

Brilliant! The more of your videos I watched the more impressed I am with Power Query's capabilities, to say nothing of the easy-to-follow and effective teaching methods you employ. Great work, and thank you! I am making a mental note of the work I can save, having previously used a witches' brew of clunky formulae to harvest data that I have to re-familiarize with each time I do the occasional work.

Pity I can only upvote you once on this.