Unpivot date data

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,639
Office Version
  1. 365
Platform
  1. Windows
Can someone please help me? My data in a huge XLSM file looks like this, with a column for the year, a column for the month and a row containing the day all the way rightways to 31 (so ignore that it's in Column H - I didn't want to post 33 columns here).

ABCDEFGH
1YearMonth12345...31
219001-14.4-11.7-12.2-12.2-6.74.4
3190024.5-2.2-7.8-12.22.8-4.4
4190036.1-2.2-14.4-16.7-17.2-8.9
51900418.918.917.817.817.821.7
61900522.818.926.124.423.318.9
71900618.322.222.823.922.218.3
8190071515.616.121.12015.6

<tbody>
</tbody>
max



I wish to use Power Query to transform the data to this, with a column for the date and a column for the value. But I don't know how - can someone walk me through it please?

AB
1Datevalue
21/1/1900-14.4
31/2/1900-11.7
41/3/1900-12.2
51/4/1900-12.2
61/5/1900-6.7
7
81/31/19007.2
92/1/19004.5

<tbody>
</tbody>
Sheet4
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hello,

select your table PQ/Excel Data/From Table - > select first two columns (Years and Month) and from Transform/Unpivot other columns

(and here "Attribute" columns would be your dates)
 
Upvote 0
Hello,

select your table PQ/Excel Data/From Table - > select first two columns (Years and Month) and from Transform/Unpivot other columns

(and here "Attribute" columns would be your dates)


1. Select columns with day number and then select unpivot (not as suggested above!)
2. Change "Attribute" to Data Type Whole Number
3. Add custom column with Formula #date([Year],[Month],[Attribute]))
4. Remove not needed columns and reorganize view as you wish
 
Upvote 0
1. Select columns with day number and then select unpivot (not as suggested above!)

ralliartur be careful about unpivot step (suggested "Unpivot other columns" but not "Unpivot Columns" ). Instead of selecting 31 columns of the days it'll better to select Year and Month columns and Unpivot other columns.
 
Upvote 0
I was suggesting to use default action - keep in mind that it is not always obvious to use dropdown list in ribbon menu.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top