Basic Excel Business Analytics #36: Power Query to Unpivot Data (Multiple Columns to Single Column)

Learn about using the Unpivot feature in Power Query to take four separate columns of numbers with columns headers that are criteria and convert it into a proper data set with a column for the criteria and a column for the numbers, and then build a PivotTable to calculate the Mean and Standard Deviation for average hours worked per week for different categories of teachers:
1) (00:17) What does Unpivot mean?
2) (01: 31) How to get data in the Excel workbook that contains the Power Query Query into Power Query: either: 1) Convert data to an Excel Table, or Convert data to a Filtered List (using Ctrl + Shift + L, L)
3) (02:56) Unpivot feature in Power Query.
4) (03:13) Rename columns
5) (03:26) Close and Load to a Table in the Excel worksheet.
6) (03:50) Now you can Filter or Sort the Proper Data Set
7) (04:12) Build PivotTable to calculate the Mean and Standard Deviation for average hours worked per week for different categories of teachers
8) (05:53) Summary

Download Excel File Not: After clicking on link, Use Ctrl + F (Find) and search for “Highline BI 348 Class” or for the file name as seen at the beginning of the video.

[danieldainty]

What a great trick - this will really help analyzing data in Power BI. Thanks for the trick

[forzaalgiers]

Mike, You are a ONE Man Team, Great Videos....Thank you

[janetgong]

best excel instructor ever! thank you so much!

[douglaszulu]

Hi Mike. Thanks for the videos on Power Query and Power Pivot. Been a great help and useful to reduce my workload from a days work to just minutes. I have a report of raw data I receive which I cannot share but it is structure wrong with a lot of data: field headers in columns are week1 to week 23 after a lot of rows upto 2000 then at the bottom week 24 in the same column as week 1, week 25 in same column as week2 till week46.In power query. How do I move weeks 24 to 46 to continue a column next to week 23?

[vijaypalmanit]

Hi Mike, I didn't get the the hack Ctrl+Shift+L, and its not working when clicking on 'From Table'
it throws the error : Expression.Error: We couldn't find an Excel table named
Details: