DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,639
- Office Version
- 365
- Platform
- 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).
<tbody>
</tbody>
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?
<tbody>
</tbody>
A | B | C | D | E | F | G | H | ||
---|---|---|---|---|---|---|---|---|---|
1 | Year | Month | 1 | 2 | 3 | 4 | 5 | ... | 31 |
2 | 1900 | 1 | -14.4 | -11.7 | -12.2 | -12.2 | -6.7 | 4.4 | |
3 | 1900 | 2 | 4.5 | -2.2 | -7.8 | -12.2 | 2.8 | -4.4 | |
4 | 1900 | 3 | 6.1 | -2.2 | -14.4 | -16.7 | -17.2 | -8.9 | |
5 | 1900 | 4 | 18.9 | 18.9 | 17.8 | 17.8 | 17.8 | 21.7 | |
6 | 1900 | 5 | 22.8 | 18.9 | 26.1 | 24.4 | 23.3 | 18.9 | |
7 | 1900 | 6 | 18.3 | 22.2 | 22.8 | 23.9 | 22.2 | 18.3 | |
8 | 1900 | 7 | 15 | 15.6 | 16.1 | 21.1 | 20 | 15.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?
A | B | |
---|---|---|
1 | Date | value |
2 | 1/1/1900 | -14.4 |
3 | 1/2/1900 | -11.7 |
4 | 1/3/1900 | -12.2 |
5 | 1/4/1900 | -12.2 |
6 | 1/5/1900 | -6.7 |
7 | … | |
8 | 1/31/1900 | 7.2 |
9 | 2/1/1900 | 4.5 |
<tbody>
</tbody>
Sheet4