Welcome to Excel Jet Consult blog post. In this video, I demo how to unpivot columns in Excel and SQL Server. If you enjoy this video, share, like, comment and subscribe
Video
Kindly watch the video tutorial below
Unpivot Columns in Power Query
Unpivot Columns simply means to rotate dataset from columns into rows. We have the sample sales data that is officially formatted as an Excel Tables using CTRL + L. You can format using the traditional CTRL + T after selecting a single cell within the dataset. The goal is to have three columns for Account Manager, Month, Sales Amount
Next, we need to get the data into the Power Query. To do that,
Right-click in any cell and select Get Data from Sheet or Table/Range
or
In the Get & Transform Data of the Data tab, select From Sheet or Table/Range
The Power Query editor opens as seen below
Next, we select the Account Manager column
Right-click and select Unpivot other columns
From the caption below, the dataset is unpivoted. Now, we have the 3 columns. So easy
We can then load the data to Excel or use it in the data model
Unpivot using SQL Server
We want to achieve the same result using SQL Server. In the screenshot below, I wrote a simple Select all statement to retrieve the data from my Database
The goal remains the same. We are are going to write relatively advanced query and subquery as seen below
select [Acct Managers], Month, Sales from
(select [Acct Managers], January, February, March, April, May, June,
July, August, September, October, November, December from Sales) SpecialData
unpivot
(sales for month in (January, February, March, April, May, June,
July, August, September, October, November, December)) as FinalResult
Select the query and click on Execute or F5 to run. From the capture below, the dataset has been rotated and we have three columns and exactly the result we had in Excel. Be sure to watch the video for better explanation
See you in the next tutorial