How to UNPIVOT Data with Excel Power Query

How do you use Power Query to unpivot data in Excel (Excel 2013 upwards)? One common data layout problem that you'll encounter in the workplace is where you have data with multiple headers or where the headers represent the same attribute about the data.

For instance, it is very common to have budget line items row-wise and the monthly figures shown across 12 columns. the BIG CHALLENGE with this cross-tabulated data structure is that for you to analyze the values using Pivot Tables, you have to drag and drop the fields 12 times. Further, if you need to view the report for a given month and give users the ability to switch to another month, that would not be possible, at least not intuitive or dynamic.

For optimal data structure, such data need to be unpivoted to have a single column with months and a single column with the budget figures.

This video describes how you can save loads of time to get your data ready for analysis. You'll learn how to use Excel Power Query to unpivot columns of data (sometimes called flattening the data) to make it pivot-friendly (tabular layout).

The beauty? The steps you do will be recorded so that you don't have to repeat it with additional data!

You can download the file that goes with this video here:

Remember to show your love by sharing this video and subscribing!

0:00 Data Structure Problem
4:01 Convert to Official Excel Table Format
5:34 Load data to Power Query
7:50 Filter Empty Rows and Fill Blank Cells
9:29 Unpivoting Columns of Data
12:12 Merging Queries (VLOOKUP equivalent)
14:22 Custom Calculated Column
15:22 Load back the Clean Data
16:00 Create a Pivot Table Summary

#powerquery #unpivotdata #powerbi

//Watch this next on how to combine multiple Excel files and worksheets:

[Howtoexcel]

You are the best I just finished watching the power bi tutorial and my only challenge was unpivot and this tutorial above has sorted that. I'm grateful for your good work 👍