Difficulty transposing and then stacking time series data

[Hawk11ns]

I run a daily report and the data sources are all organized differently. For this particular example, my goal is to drop the data in cell A1, run a macro, and auto-clean/organize accordingly. Below is the data as it is entered (note the date range (number of rows) can differ, steps I believe need to be taken, and the end result I am looking for.

Beginning Data:

OperatingDate

Zone

DataType

Total

0:00​

1:00​

2:00​

3:00​

4:00​

5:00​

6:00​

7:00​

8:00​

9:00​

10:00​

11:00​

12:00​

13:00​

14:00​

15:00​

16:00​

17:00​

18:00​

19:00​

20:00​

21:00​

22:00​

23:00​

5/13/2020​

LZ_HOUSTON

Forecast (without Losses)

337.6​

11.1​

9.8​

8.9​

8.2​

7.9​

8.4​

9.2​

8.9​

8.9​

9.5​

11.3​

13​

14.9​

16.8​

18.3​

19.7​

20.8​

21.4​

21.4​

19.9​

19.6​

18.8​

16.8​

14​

5/13/2020​

LZ_HOUSTON

Forecast (with Losses)

361.3​

11.9​

10.5​

9.6​

8.7​

8.5​

8.9​

9.8​

9.5​

9.5​

10.2​

12.1​

13.9​

16​

18​

19.5​

21.1​

22.3​

23​

22.9​

21.3​

21​

20.1​

18​

15​

5/13/2020​

LZ_HOUSTON

Actual Load (without Losses)

350.12​

11.35​

10.15​

9.19​

8.58​

8.06​

7.65​

7.59​

8.06​

9.2​

10.71​

12.76​

15.21​

17.66​

19.33​

20.17​

20.81​

21.59​

21.8​

21.55​

20.21​

19.3​

18.23​

16.57​

14.38​

5/13/2020​

LZ_HOUSTON

Actual Load (with Losses)

374.85​

11.96​

10.73​

9.75​

9.14​

8.56​

8.14​

8.07​

8.6​

9.88​

11.56​

13.73​

16.3​

18.82​

20.61​

21.62​

22.31​

23.16​

23.32​

23.12​

21.68​

20.72​

19.67​

17.87​

15.51​

5/13/2020​

LZ_NORTH

Forecast (without Losses)

239.5​

7.7​

6.6​

6​

5.7​

5.6​

6.2​

7.6​

8.5​

7.5​

7.3​

7.7​

8.5​

9.2​

10.2​

11.1​

12.2​

13.7​

14.7​

15.3​

14.9​

14.6​

14.7​

13.1​

10.8​

5/13/2020​

LZ_NORTH

Forecast (with Losses)

256.2​

8.2​

7​

6.5​

6.1​

6​

6.6​

8.1​

9.1​

8​

7.8​

8.3​

9.1​

9.9​

10.9​

11.9​

13.1​

14.6​

15.8​

16.4​

15.9​

15.7​

15.7​

14​

11.5​

5/13/2020​

LZ_NORTH

Actual Load (without Losses)

229.33​

6.94​

5.98​

5.33​

4.97​

4.88​

4.85​

5.21​

6.03​

6.91​

7.59​

8.39​

8.97​

9.56​

10​

10.73​

11.87​

13.12​

14.84​

15.71​

15.32​

14.54​

14.04​

12.68​

10.88​

5/13/2020​

LZ_NORTH

Actual Load (with Losses)

244.1​

7.26​

6.27​

5.61​

5.25​

5.14​

5.11​

5.5​

6.38​

7.37​

8.14​

8.97​

9.55​

10.13​

10.6​

11.44​

12.66​

14​

15.79​

16.76​

16.34​

15.52​

15.06​

13.6​

11.65​

5/13/2020​

LZ_SOUTH

Forecast (without Losses)

38.5​

1.3​

1.2​

1.1​

1.1​

1.1​

1.1​

1.3​

1.3​

1.2​

1.3​

1.4​

1.5​

1.6​

1.7​

1.8​

2​

2.1​

2.2​

2.2​

2.2​

2.1​

2.1​

1.9​

1.6​

5/13/2020​

LZ_SOUTH

Forecast (with Losses)

41.2​

1.4​

1.3​

1.2​

1.2​

1.1​

1.2​

1.4​

1.4​

1.3​

1.4​

1.5​

1.6​

1.7​

1.9​

2​

2.1​

2.3​

2.4​

2.4​

2.3​

2.3​

2.3​

2​

1.7​

5/13/2020​

LZ_SOUTH

Actual Load (without Losses)

27.34​

0.85​

0.74​

0.68​

0.59​

0.6​

0.6​

0.6​

0.7​

0.76​

0.81​

0.92​

1.06​

1.16​

1.27​

1.43​

1.52​

1.66​

1.84​

1.85​

1.8​

1.66​

1.59​

1.43​

1.22​

5/13/2020​

LZ_SOUTH

Actual Load (with Losses)

29.41​

0.89​

0.78​

0.72​

0.64​

0.64​

0.64​

0.64​

0.74​

0.82​

0.88​

1​

1.14​

1.24​

1.36​

1.55​

1.64​

1.79​

1.97​

2​

1.93​

1.79​

1.72​

1.55​

1.32​

5/13/2020​

LZ_WEST

Forecast (without Losses)

58.8​

2.2​

2.2​

2.1​

2.1​

2.1​

2.2​

2.3​

2.4​

2.4​

2.4​

2.5​

2.5​

2.6​

2.6​

2.7​

2.7​

2.7​

2.7​

2.7​

2.7​

2.6​

2.6​

2.5​

2.4​

5/13/2020​

LZ_WEST

Forecast (with Losses)

63​

2.4​

2.3​

2.3​

2.2​

2.2​

2.3​

2.5​

2.6​

2.6​

2.6​

2.7​

2.7​

2.8​

2.8​

2.8​

2.9​

2.9​

2.9​

2.9​

2.8​

2.8​

2.8​

2.7​

2.5​

5/13/2020​

LZ_WEST

Actual Load (without Losses)

9.38​

0.32​

0.27​

0.23​

0.21​

0.2​

0.2​

0.21​

0.25​

0.26​

0.28​

0.29​

0.32​

0.36​

0.41​

0.49​

0.56​

0.63​

0.68​

0.7​

0.62​

0.55​

0.53​

0.44​

0.37​

5/13/2020​

LZ_WEST

Actual Load (with Losses)

10.03​

0.33​

0.28​

0.24​

0.23​

0.21​

0.21​

0.22​

0.27​

0.28​

0.3​

0.32​

0.34​

0.39​

0.44​

0.52​

0.6​

0.67​

0.73​

0.75​

0.67​

0.59​

0.57​

0.48​

0.4​

5/14/2020​

LZ_HOUSTON

Forecast (without Losses)

344.7​

11.9​

10.5​

9.5​

8.8​

8.6​

8.9​

9.5​

9.2​

9.4​

9.8​

11.7​

13.3​

15.3​

17​

18.2​

19.5​

20.4​

21​

20.9​

20​

19.8​

19.2​

17.3​

14.7​

5/14/2020​

LZ_HOUSTON

Forecast (with Losses)

368.8​

12.8​

11.2​

10.2​

9.5​

9.2​

9.6​

10.2​

9.9​

10​

10.5​

12.5​

14.2​

16.4​

18.2​

19.5​

20.8​

21.8​

22.4​

22.4​

21.4​

21.2​

20.6​

18.5​

15.8​

5/14/2020​

LZ_HOUSTON

Actual Load (without Losses)

388.43​

12.6​

11.21​

10.23​

9.52​

9.02​

8.65​

8.57​

9.2​

10.51​

12.47​

14.49​

16.67​

18.82​

20.73​

22.49​

24.03​

24.79​

25.31​

24.54​

22.53​

20.86​

19.39​

17.22​

14.59​

5/14/2020​

LZ_HOUSTON

Actual Load (with Losses)

416.85​

13.52​

12.07​

11.04​

10.3​

9.77​

9.37​

9.28​

9.99​

11.41​

13.56​

15.66​

17.92​

20.2​

22.25​

24.09​

25.72​

26.5​

27.12​

26.26​

23.98​

22.08​

20.69​

18.4​

15.66​

5/14/2020​

LZ_NORTH

Forecast (without Losses)

269.7​

8.6​

7.4​

6.8​

6.4​

6.3​

6.9​

8.3​

9.1​

8.2​

8​

8.9​

10.1​

11.2​

12.3​

13.3​

14.3​

15.6​

16.4​

16.9​

16.4​

16​

16.1​

14.4​

12​

5/14/2020​

LZ_NORTH

Forecast (with Losses)

288.6​

9.2​

7.9​

7.3​

6.8​

6.7​

7.3​

8.8​

9.8​

8.8​

8.6​

9.5​

10.8​

12​

13.2​

14.2​

15.3​

16.7​

17.5​

18​

17.5​

17.1​

17.2​

15.4​

12.8​

5/14/2020​

LZ_NORTH

Actual Load (without Losses)

294.59​

9.15​

7.87​

7.01​

6.47​

6.07​

5.79​

5.92​

6.6​

7.55​

8.55​

9.82​

11.65​

13.34​

14.76​

16.08​

17.55​

18.9​

20.02​

20.11​

18.97​

17.62​

16.74​

15.1​

12.95​

5/14/2020​

LZ_NORTH

Actual Load (with Losses)

314.16​

9.75​

8.41​

7.51​

6.94​

6.52​

6.22​

6.36​

7.12​

8.14​

9.24​

10.55​

12.45​

14.25​

15.76​

17.14​

18.68​

20.11​

21.35​

21.41​

20.08​

18.55​

17.76​

16.04​

13.81​

5/14/2020​

LZ_SOUTH

Forecast (without Losses)

40​

1.4​

1.2​

1.2​

1.1​

1.1​

1.1​

1.3​

1.3​

1.3​

1.3​

1.4​

1.6​

1.7​

1.8​

1.9​

2.1​

2.2​

2.3​

2.3​

2.2​

2.2​

2.2​

2​

1.7​

5/14/2020​

LZ_SOUTH

Forecast (with Losses)

42.8​

1.5​

1.3​

1.3​

1.2​

1.2​

1.2​

1.4​

1.4​

1.4​

1.4​

1.5​

1.7​

1.8​

1.9​

2.1​

2.2​

2.3​

2.5​

2.5​

2.4​

2.4​

2.3​

2.1​

1.8​

5/14/2020​

LZ_SOUTH

Actual Load (without Losses)

30.93​

1.06​

0.94​

0.85​

0.78​

0.73​

0.7​

0.7​

0.75​

0.78​

0.84​

0.95​

1.13​

1.35​

1.53​

1.66​

1.77​

1.92​

2.08​

2.04​

1.95​

1.88​

1.72​

1.52​

1.33​

5/14/2020​

LZ_SOUTH

Actual Load (with Losses)

33.31​

1.14​

1.01​

0.91​

0.84​

0.79​

0.76​

0.76​

0.82​

0.85​

0.92​

1.04​

1.22​

1.46​

1.65​

1.79​

1.91​

2.07​

2.23​

2.19​

2.08​

1.99​

1.84​

1.63​

1.43​

5/14/2020​

LZ_WEST

Forecast (without Losses)

60.2​

2.3​

2.2​

2.1​

2.1​

2.1​

2.2​

2.3​

2.4​

2.4​

2.5​

2.5​

2.6​

2.6​

2.7​

2.7​

2.8​

2.8​

2.8​

2.8​

2.8​

2.7​

2.7​

2.6​

2.4​

5/14/2020​

LZ_WEST

Forecast (with Losses)

64.5​

2.4​

2.3​

2.3​

2.3​

2.3​

2.3​

2.5​

2.6​

2.6​

2.6​

2.7​

2.8​

2.8​

2.9​

2.9​

3​

3​

3​

3​

3​

2.9​

2.9​

2.8​

2.6​

5/14/2020​

LZ_WEST

Actual Load (without Losses)

10.91​

0.52​

0.25​

0.22​

0.2​

0.18​

0.19​

0.21​

0.24​

0.28​

0.32​

0.35​

0.41​

0.47​

0.54​

0.63​

0.7​

0.72​

0.77​

0.73​

0.7​

0.64​

0.61​

0.55​

0.47​

5/14/2020​

LZ_WEST

Actual Load (with Losses)

11.69​

0.55​

0.27​

0.24​

0.21​

0.2​

0.21​

0.22​

0.26​

0.3​

0.35​

0.38​

0.45​

0.5​

0.58​

0.67​

0.75​

0.77​

0.82​

0.78​

0.74​

0.68​

0.65​

0.59​

0.51​

- Delete Column D "Total"
- Transpose the data, but row stack by date
- Enter "DATE" in cell A2 and "HE" in cell B2

LZ_HOUSTON

LZ_HOUSTON

LZ_HOUSTON

LZ_HOUSTON

LZ_NORTH

LZ_NORTH

LZ_NORTH

LZ_NORTH

LZ_SOUTH

LZ_SOUTH

LZ_SOUTH

LZ_SOUTH

LZ_WEST

LZ_WEST

LZ_WEST

LZ_WEST

DATE

HE

Forecast (without Losses)

Forecast (with Losses)

Actual Load (without Losses)

Actual Load (with Losses)

Forecast (without Losses)

Forecast (with Losses)

Actual Load (without Losses)

Actual Load (with Losses)

Forecast (without Losses)

Forecast (with Losses)

Actual Load (without Losses)

Actual Load (with Losses)

Forecast (without Losses)

Forecast (with Losses)

Actual Load (without Losses)

Actual Load (with Losses)

5/13/2020​

0:00​

11.1​

11.9​

11.35​

11.96​

7.7​

8.2​

6.94​

7.26​

1.3​

1.4​

0.85​

0.89​

2.2​

2.4​

0.32​

0.33​

5/13/2020​

1:00​

9.8​

10.5​

10.15​

10.73​

6.6​

7​

5.98​

6.27​

1.2​

1.3​

0.74​

0.78​

2.2​

2.3​

0.27​

0.28​

5/13/2020​

2:00​

8.9​

9.6​

9.19​

9.75​

6​

6.5​

5.33​

5.61​

1.1​

1.2​

0.68​

0.72​

2.1​

2.3​

0.23​

0.24​

5/13/2020​

3:00​

8.2​

8.7​

8.58​

9.14​

5.7​

6.1​

4.97​

5.25​

1.1​

1.2​

0.59​

0.64​

2.1​

2.2​

0.21​

0.23​

5/13/2020​

4:00​

7.9​

8.5​

8.06​

8.56​

5.6​

6​

4.88​

5.14​

1.1​

1.1​

0.6​

0.64​

2.1​

2.2​

0.2​

0.21​

5/13/2020​

5:00​

8.4​

8.9​

7.65​

8.14​

6.2​

6.6​

4.85​

5.11​

1.1​

1.2​

0.6​

0.64​

2.2​

2.3​

0.2​

0.21​

5/13/2020​

6:00​

9.2​

9.8​

7.59​

8.07​

7.6​

8.1​

5.21​

5.5​

1.3​

1.4​

0.6​

0.64​

2.3​

2.5​

0.21​

0.22​

5/13/2020​

7:00​

8.9​

9.5​

8.06​

8.6​

8.5​

9.1​

6.03​

6.38​

1.3​

1.4​

0.7​

0.74​

2.4​

2.6​

0.25​

0.27​

5/13/2020​

8:00​

8.9​

9.5​

9.2​

9.88​

7.5​

8​

6.91​

7.37​

1.2​

1.3​

0.76​

0.82​

2.4​

2.6​

0.26​

0.28​

5/13/2020​

9:00​

9.5​

10.2​

10.71​

11.56​

7.3​

7.8​

7.59​

8.14​

1.3​

1.4​

0.81​

0.88​

2.4​

2.6​

0.28​

0.3​

5/13/2020​

10:00​

11.3​

12.1​

12.76​

13.73​

7.7​

8.3​

8.39​

8.97​

1.4​

1.5​

0.92​

1​

2.5​

2.7​

0.29​

0.32​

5/13/2020​

11:00​

13​

13.9​

15.21​

16.3​

8.5​

9.1​

8.97​

9.55​

1.5​

1.6​

1.06​

1.14​

2.5​

2.7​

0.32​

0.34​

5/13/2020​

12:00​

14.9​

16​

17.66​

18.82​

9.2​

9.9​

9.56​

10.13​

1.6​

1.7​

1.16​

1.24​

2.6​

2.8​

0.36​

0.39​

5/13/2020​

13:00​

16.8​

18​

19.33​

20.61​

10.2​

10.9​

10​

10.6​

1.7​

1.9​

1.27​

1.36​

2.6​

2.8​

0.41​

0.44​

5/13/2020​

14:00​

18.3​

19.5​

20.17​

21.62​

11.1​

11.9​

10.73​

11.44​

1.8​

2​

1.43​

1.55​

2.7​

2.8​

0.49​

0.52​

5/13/2020​

15:00​

19.7​

21.1​

20.81​

22.31​

12.2​

13.1​

11.87​

12.66​

2​

2.1​

1.52​

1.64​

2.7​

2.9​

0.56​

0.6​

5/13/2020​

16:00​

20.8​

22.3​

21.59​

23.16​

13.7​

14.6​

13.12​

14​

2.1​

2.3​

1.66​

1.79​

2.7​

2.9​

0.63​

0.67​

5/13/2020​

17:00​

21.4​

23​

21.8​

23.32​

14.7​

15.8​

14.84​

15.79​

2.2​

2.4​

1.84​

1.97​

2.7​

2.9​

0.68​

0.73​

5/13/2020​

18:00​

21.4​

22.9​

21.55​

23.12​

15.3​

16.4​

15.71​

16.76​

2.2​

2.4​

1.85​

2​

2.7​

2.9​

0.7​

0.75​

5/13/2020​

19:00​

19.9​

21.3​

20.21​

21.68​

14.9​

15.9​

15.32​

16.34​

2.2​

2.3​

1.8​

1.93​

2.7​

2.8​

0.62​

0.67​

5/13/2020​

20:00​

19.6​

21​

19.3​

20.72​

14.6​

15.7​

14.54​

15.52​

2.1​

2.3​

1.66​

1.79​

2.6​

2.8​

0.55​

0.59​

5/13/2020​

21:00​

18.8​

20.1​

18.23​

19.67​

14.7​

15.7​

14.04​

15.06​

2.1​

2.3​

1.59​

1.72​

2.6​

2.8​

0.53​

0.57​

5/13/2020​

22:00​

16.8​

18​

16.57​

17.87​

13.1​

14​

12.68​

13.6​

1.9​

2​

1.43​

1.55​

2.5​

2.7​

0.44​

0.48​

5/13/2020​

23:00​

14​

15​

14.38​

15.51​

10.8​

11.5​

10.88​

11.65​

1.6​

1.7​

1.22​

1.32​

2.4​

2.5​

0.37​

0.4​

5/13/2020​

0:00​

11.9​

12.8​

12.6​

13.52​

8.6​

9.2​

9.15​

9.75​

1.4​

1.5​

1.06​

1.14​

2.3​

2.4​

0.52​

0.55​

5/14/2020​

1:00​

10.5​

11.2​

11.21​

12.07​

7.4​

7.9​

7.87​

8.41​

1.2​

1.3​

0.94​

1.01​

2.2​

2.3​

0.25​

0.27​

5/14/2020​

2:00​

9.5​

10.2​

10.23​

11.04​

6.8​

7.3​

7.01​

7.51​

1.2​

1.3​

0.85​

0.91​

2.1​

2.3​

0.22​

0.24​

5/14/2020​

3:00​

8.8​

9.5​

9.52​

10.3​

6.4​

6.8​

6.47​

6.94​

1.1​

1.2​

0.78​

0.84​

2.1​

2.3​

0.2​

0.21​

5/14/2020​

4:00​

8.6​

9.2​

9.02​

9.77​

6.3​

6.7​

6.07​

6.52​

1.1​

1.2​

0.73​

0.79​

2.1​

2.3​

0.18​

0.2​

5/14/2020​

5:00​

8.9​

9.6​

8.65​

9.37​

6.9​

7.3​

5.79​

6.22​

1.1​

1.2​

0.7​

0.76​

2.2​

2.3​

0.19​

0.21​

5/14/2020​

6:00​

9.5​

10.2​

8.57​

9.28​

8.3​

8.8​

5.92​

6.36​

1.3​

1.4​

0.7​

0.76​

2.3​

2.5​

0.21​

0.22​

5/14/2020​

7:00​

9.2​

9.9​

9.2​

9.99​

9.1​

9.8​

6.6​

7.12​

1.3​

1.4​

0.75​

0.82​

2.4​

2.6​

0.24​

0.26​

5/14/2020​

8:00​

9.4​

10​

10.51​

11.41​

8.2​

8.8​

7.55​

8.14​

1.3​

1.4​

0.78​

0.85​

2.4​

2.6​

0.28​

0.3​

5/14/2020​

9:00​

9.8​

10.5​

12.47​

13.56​

8​

8.6​

8.55​

9.24​

1.3​

1.4​

0.84​

0.92​

2.5​

2.6​

0.32​

0.35​

5/14/2020​

10:00​

11.7​

12.5​

14.49​

15.66​

8.9​

9.5​

9.82​

10.55​

1.4​

1.5​

0.95​

1.04​

2.5​

2.7​

0.35​

0.38​

5/14/2020​

11:00​

13.3​

14.2​

16.67​

17.92​

10.1​

10.8​

11.65​

12.45​

1.6​

1.7​

1.13​

1.22​

2.6​

2.8​

0.41​

0.45​

5/14/2020​

12:00​

15.3​

16.4​

18.82​

20.2​

11.2​

12​

13.34​

14.25​

1.7​

1.8​

1.35​

1.46​

2.6​

2.8​

0.47​

0.5​

5/14/2020​

13:00​

17​

18.2​

20.73​

22.25​

12.3​

13.2​

14.76​

15.76​

1.8​

1.9​

1.53​

1.65​

2.7​

2.9​

0.54​

0.58​

5/14/2020​

14:00​

18.2​

19.5​

22.49​

24.09​

13.3​

14.2​

16.08​

17.14​

1.9​

2.1​

1.66​

1.79​

2.7​

2.9​

0.63​

0.67​

5/14/2020​

15:00​

19.5​

20.8​

24.03​

25.72​

14.3​

15.3​

17.55​

18.68​

2.1​

2.2​

1.77​

1.91​

2.8​

3​

0.7​

0.75​

5/14/2020​

16:00​

20.4​

21.8​

24.79​

26.5​

15.6​

16.7​

18.9​

20.11​

2.2​

2.3​

1.92​

2.07​

2.8​

3​

0.72​

0.77​

5/14/2020​

17:00​

21​

22.4​

25.31​

27.12​

16.4​

17.5​

20.02​

21.35​

2.3​

2.5​

2.08​

2.23​

2.8​

3​

0.77​

0.82​

5/14/2020​

18:00​

20.9​

22.4​

24.54​

26.26​

16.9​

18​

20.11​

21.41​

2.3​

2.5​

2.04​

2.19​

2.8​

3​

0.73​

0.78​

5/14/2020​

19:00​

20​

21.4​

22.53​

23.98​

16.4​

17.5​

18.97​

20.08​

2.2​

2.4​

1.95​

2.08​

2.8​

3​

0.7​

0.74​

5/14/2020​

20:00​

19.8​

21.2​

20.86​

22.08​

16​

17.1​

17.62​

18.55​

2.2​

2.4​

1.88​

1.99​

2.7​

2.9​

0.64​

0.68​

5/14/2020​

21:00​

19.2​

20.6​

19.39​

20.69​

16.1​

17.2​

16.74​

17.76​

2.2​

2.3​

1.72​

1.84​

2.7​

2.9​

0.61​

0.65​

5/14/2020​

22:00​

17.3​

18.5​

17.22​

18.4​

14.4​

15.4​

15.1​

16.04​

2​

2.1​

1.52​

1.63​

2.6​

2.8​

0.55​

0.59​

5/14/2020​

23:00​

14.7​

15.8​

14.59​

15.66​

12​

12.8​

12.95​

13.81​

1.7​

1.8​

1.33​

1.43​

2.4​

2.6​

0.47​

0.51​

- Delete columns R, Q, O, N, M, K, J, I, G, F, E, C

		LZ_HOUSTON	LZ_NORTH	LZ_SOUTH	LZ_WEST
DATE	HE	Forecast (with Losses)	Forecast (with Losses)	Forecast (with Losses)	Forecast (with Losses)
5/13/2020​	0:00​	11.9​	8.2​	1.4​	2.4​
5/13/2020​	1:00​	10.5​	7​	1.3​	2.3​
5/13/2020​	2:00​	9.6​	6.5​	1.2​	2.3​
5/13/2020​	3:00​	8.7​	6.1​	1.2​	2.2​
5/13/2020​	4:00​	8.5​	6​	1.1​	2.2​
5/13/2020​	5:00​	8.9​	6.6​	1.2​	2.3​
5/13/2020​	6:00​	9.8​	8.1​	1.4​	2.5​
5/13/2020​	7:00​	9.5​	9.1​	1.4​	2.6​
5/13/2020​	8:00​	9.5​	8​	1.3​	2.6​
5/13/2020​	9:00​	10.2​	7.8​	1.4​	2.6​
5/13/2020​	10:00​	12.1​	8.3​	1.5​	2.7​
5/13/2020​	11:00​	13.9​	9.1​	1.6​	2.7​
5/13/2020​	12:00​	16​	9.9​	1.7​	2.8​
5/13/2020​	13:00​	18​	10.9​	1.9​	2.8​
5/13/2020​	14:00​	19.5​	11.9​	2​	2.8​
5/13/2020​	15:00​	21.1​	13.1​	2.1​	2.9​
5/13/2020​	16:00​	22.3​	14.6​	2.3​	2.9​
5/13/2020​	17:00​	23​	15.8​	2.4​	2.9​
5/13/2020​	18:00​	22.9​	16.4​	2.4​	2.9​
5/13/2020​	19:00​	21.3​	15.9​	2.3​	2.8​
5/13/2020​	20:00​	21​	15.7​	2.3​	2.8​
5/13/2020​	21:00​	20.1​	15.7​	2.3​	2.8​
5/13/2020​	22:00​	18​	14​	2​	2.7​
5/13/2020​	23:00​	15​	11.5​	1.7​	2.5​
5/13/2020​	0:00​	12.8​	9.2​	1.5​	2.4​
5/14/2020​	1:00​	11.2​	7.9​	1.3​	2.3​
5/14/2020​	2:00​	10.2​	7.3​	1.3​	2.3​
5/14/2020​	3:00​	9.5​	6.8​	1.2​	2.3​
5/14/2020​	4:00​	9.2​	6.7​	1.2​	2.3​
5/14/2020​	5:00​	9.6​	7.3​	1.2​	2.3​
5/14/2020​	6:00​	10.2​	8.8​	1.4​	2.5​
5/14/2020​	7:00​	9.9​	9.8​	1.4​	2.6​
5/14/2020​	8:00​	10​	8.8​	1.4​	2.6​
5/14/2020​	9:00​	10.5​	8.6​	1.4​	2.6​
5/14/2020​	10:00​	12.5​	9.5​	1.5​	2.7​
5/14/2020​	11:00​	14.2​	10.8​	1.7​	2.8​
5/14/2020​	12:00​	16.4​	12​	1.8​	2.8​
5/14/2020​	13:00​	18.2​	13.2​	1.9​	2.9​
5/14/2020​	14:00​	19.5​	14.2​	2.1​	2.9​
5/14/2020​	15:00​	20.8​	15.3​	2.2​	3​
5/14/2020​	16:00​	21.8​	16.7​	2.3​	3​
5/14/2020​	17:00​	22.4​	17.5​	2.5​	3​
5/14/2020​	18:00​	22.4​	18​	2.5​	3​
5/14/2020​	19:00​	21.4​	17.5​	2.4​	3​
5/14/2020​	20:00​	21.2​	17.1​	2.4​	2.9​
5/14/2020​	21:00​	20.6​	17.2​	2.3​	2.9​
5/14/2020​	22:00​	18.5​	15.4​	2.1​	2.8​
5/14/2020​	23:00​	15.8​	12.8​	1.8​	2.6​

- Copy Cells 'C1:G' and Paste in C2
- Delete empty row

DATE	HE	LZ_HOUSTON	LZ_NORTH	LZ_SOUTH	LZ_WEST
5/13/2020​	0:00​	11.9​	8.2​	1.4​	2.4​
5/13/2020​	1:00​	10.5​	7​	1.3​	2.3​
5/13/2020​	2:00​	9.6​	6.5​	1.2​	2.3​
5/13/2020​	3:00​	8.7​	6.1​	1.2​	2.2​
5/13/2020​	4:00​	8.5​	6​	1.1​	2.2​
5/13/2020​	5:00​	8.9​	6.6​	1.2​	2.3​
5/13/2020​	6:00​	9.8​	8.1​	1.4​	2.5​
5/13/2020​	7:00​	9.5​	9.1​	1.4​	2.6​
5/13/2020​	8:00​	9.5​	8​	1.3​	2.6​
5/13/2020​	9:00​	10.2​	7.8​	1.4​	2.6​
5/13/2020​	10:00​	12.1​	8.3​	1.5​	2.7​
5/13/2020​	11:00​	13.9​	9.1​	1.6​	2.7​
5/13/2020​	12:00​	16​	9.9​	1.7​	2.8​
5/13/2020​	13:00​	18​	10.9​	1.9​	2.8​
5/13/2020​	14:00​	19.5​	11.9​	2​	2.8​
5/13/2020​	15:00​	21.1​	13.1​	2.1​	2.9​
5/13/2020​	16:00​	22.3​	14.6​	2.3​	2.9​
5/13/2020​	17:00​	23​	15.8​	2.4​	2.9​
5/13/2020​	18:00​	22.9​	16.4​	2.4​	2.9​
5/13/2020​	19:00​	21.3​	15.9​	2.3​	2.8​
5/13/2020​	20:00​	21​	15.7​	2.3​	2.8​
5/13/2020​	21:00​	20.1​	15.7​	2.3​	2.8​
5/13/2020​	22:00​	18​	14​	2​	2.7​
5/13/2020​	23:00​	15​	11.5​	1.7​	2.5​
5/13/2020​	0:00​	12.8​	9.2​	1.5​	2.4​
5/14/2020​	1:00​	11.2​	7.9​	1.3​	2.3​
5/14/2020​	2:00​	10.2​	7.3​	1.3​	2.3​
5/14/2020​	3:00​	9.5​	6.8​	1.2​	2.3​
5/14/2020​	4:00​	9.2​	6.7​	1.2​	2.3​
5/14/2020​	5:00​	9.6​	7.3​	1.2​	2.3​
5/14/2020​	6:00​	10.2​	8.8​	1.4​	2.5​
5/14/2020​	7:00​	9.9​	9.8​	1.4​	2.6​
5/14/2020​	8:00​	10​	8.8​	1.4​	2.6​
5/14/2020​	9:00​	10.5​	8.6​	1.4​	2.6​
5/14/2020​	10:00​	12.5​	9.5​	1.5​	2.7​
5/14/2020​	11:00​	14.2​	10.8​	1.7​	2.8​
5/14/2020​	12:00​	16.4​	12​	1.8​	2.8​
5/14/2020​	13:00​	18.2​	13.2​	1.9​	2.9​
5/14/2020​	14:00​	19.5​	14.2​	2.1​	2.9​
5/14/2020​	15:00​	20.8​	15.3​	2.2​	3​
5/14/2020​	16:00​	21.8​	16.7​	2.3​	3​
5/14/2020​	17:00​	22.4​	17.5​	2.5​	3​
5/14/2020​	18:00​	22.4​	18​	2.5​	3​
5/14/2020​	19:00​	21.4​	17.5​	2.4​	3​
5/14/2020​	20:00​	21.2​	17.1​	2.4​	2.9​
5/14/2020​	21:00​	20.6​	17.2​	2.3​	2.9​
5/14/2020​	22:00​	18.5​	15.4​	2.1​	2.8​
5/14/2020​	23:00​	15.8​	12.8​	1.8​	2.6​

On a side note, I tried achieving the same results using a pivot table, but I am not able to get the Hour Ending (i.e. 00:00, 01:00, etc) rows to nest under the date rows, which are nested under the Month rows.

Thanks in advance for all the help!

​

​

​

​

​

​

​

 

[sandy666]

is that what you want?

with Power Query aka Get&Transform

Date	HE	LZ_HOUSTON	LZ_NORTH	LZ_SOUTH	LZ_WEST
13/05/2020	00:00	23.86	15.46	2.29	2.73
13/05/2020	01:00	21.23	13.27	2.08	2.58
13/05/2020	02:00	19.35	12.11	1.92	2.54
13/05/2020	03:00	17.84	11.35	1.84	2.43
13/05/2020	04:00	17.06	11.14	1.74	2.41
13/05/2020	05:00	17.04	11.71	1.84	2.51
13/05/2020	06:00	17.87	13.6	2.04	2.72
13/05/2020	07:00	18.1	15.48	2.14	2.87
13/05/2020	08:00	19.38	15.37	2.12	2.88
13/05/2020	09:00	21.76	15.94	2.28	2.9
13/05/2020	10:00	25.83	17.27	2.5	3.02
13/05/2020	11:00	30.2	18.65	2.74	3.04
13/05/2020	12:00	34.82	20.03	2.94	3.19
13/05/2020	13:00	38.61	21.5	3.26	3.24
13/05/2020	14:00	41.12	23.34	3.55	3.32
13/05/2020	15:00	43.41	25.76	3.74	3.5
13/05/2020	16:00	45.46	28.6	4.09	3.57
13/05/2020	17:00	46.32	31.59	4.37	3.63
13/05/2020	18:00	46.02	33.16	4.4	3.65
13/05/2020	19:00	42.98	32.24	4.23	3.47
13/05/2020	20:00	41.72	31.22	4.09	3.39
13/05/2020	21:00	39.77	30.76	4.02	3.37
13/05/2020	22:00	35.87	27.6	3.55	3.18
13/05/2020	23:00	30.51	23.15	3.02	2.9
14/05/2020	00:00	26.32	18.95	2.64	2.95
14/05/2020	01:00	23.27	16.31	2.31	2.57
14/05/2020	02:00	21.24	14.81	2.21	2.54
14/05/2020	03:00	19.8	13.74	2.04	2.51
14/05/2020	04:00	18.97	13.22	1.99	2.5
14/05/2020	05:00	18.97	13.52	1.96	2.51
14/05/2020	06:00	19.48	15.16	2.16	2.72
14/05/2020	07:00	19.89	16.92	2.22	2.86
14/05/2020	08:00	21.41	16.94	2.25	2.9
14/05/2020	09:00	24.06	17.84	2.32	2.95
14/05/2020	10:00	28.16	20.05	2.54	3.08
14/05/2020	11:00	32.12	23.25	2.92	3.25
14/05/2020	12:00	36.6	26.25	3.26	3.3
14/05/2020	13:00	40.45	28.96	3.55	3.48
14/05/2020	14:00	43.59	31.34	3.89	3.57
14/05/2020	15:00	46.52	33.98	4.11	3.75
14/05/2020	16:00	48.3	36.81	4.37	3.77
14/05/2020	17:00	49.52	38.85	4.73	3.82
14/05/2020	18:00	48.66	39.41	4.69	3.78
14/05/2020	19:00	45.38	37.58	4.48	3.74
14/05/2020	20:00	43.28	35.65	4.39	3.58
14/05/2020	21:00	41.29	34.96	4.14	3.55
14/05/2020	22:00	36.9	31.44	3.73	3.39
14/05/2020	23:00	31.46	26.61	3.23	3.11

 

[sandy666]

I forgot to post M so here is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeDateLocale = Table.TransformColumnTypes(Source, {{"OperatingDate", type date}}, "en-US"),
    RC = Table.RemoveColumns(TypeDateLocale,{"Total"}),
    Filter = Table.SelectRows(RC, each ([DataType] = "Actual Load (with Losses)" or [DataType] = "Forecast (with Losses)")),
    UOC = Table.UnpivotOtherColumns(Filter, {"OperatingDate", "Zone", "DataType"}, "HE", "Value"),
    TypeTime = Table.TransformColumnTypes(UOC,{{"HE", type time}}),
    RC1 = Table.RemoveColumns(TypeTime,{"DataType"}),
    Pivot = Table.Pivot(RC1, List.Distinct(RC1[Zone]), "Zone", "Value", List.Sum),
    Ren = Table.RenameColumns(Pivot,{{"OperatingDate", "Date"}})
in
    Ren

 

[Hawk11ns]

@sandy666 perfect, thank you very much!!

 

[sandy666]

@sandy666 perfect, thank you very much!!

You are welcome
Thanks for the feedback
To say thanks click Like

btw. update your profile about Excel version, less troubles and wasted posts

 

[Hawk11ns]

Thanks @sandy666 ! I have another question, what would the powerquery code look like if I wanted to take multiple rows of data and have them line up against each other in a single row? For example, starting with this:

DATE	ZONE	HE1	HE2	HE3	HE4	HE5	HE6	HE7	HE8	HE9	HE10	HE11	HE12	HE13	HE14	HE15	HE16	HE17	HE18	HE19	HE20	HE21	HE22	HE23	HE24
8/18/2020​	Location A	11.71	10.18	9.33	9.54	9.10	9.11	9.37	9.38	9.66	10.72	12.16	14.18	17.76	20.46	20.73	22.34	26.15	23.21	22.43	21.43	20.62	20.35	19.15	16.68
8/19/2020​	Location A	7.91	6.81	6.13	5.56	5.60	6.31	7.67	8.10	8.46	8.86	9.05	9.89	9.16	10.09	10.70	11.84	15.88	18.29	17.65	15.56	15.32	15.10	13.91	11.69

with the end output of this in mind:

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/18/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

8/19/2020​

ZONE

HE1

HE2

HE3

HE4

HE5

HE6

HE7

HE8

HE9

HE10

HE11

HE12

HE13

HE14

HE15

HE16

HE17

HE18

HE19

HE20

HE21

HE22

HE23

HE24

HE1

HE2

HE3

HE4

HE5

HE6

HE7

HE8

HE9

HE10

HE11

HE12

HE13

HE14

HE15

HE16

HE17

HE18

HE19

HE20

HE21

HE22

HE23

HE24

Location A

11.71

10.18

9.33

9.54

9.10

9.11

9.37

9.38

9.66

10.72

12.16

14.18

17.76

20.46

20.73

22.34

26.15

23.21

22.43

21.43

20.62

20.35

19.15

16.68

7.91

6.81

6.13

5.56

5.60

6.31

7.67

8.10

8.46

8.86

9.05

9.89

9.16

10.09

10.70

11.84

15.88

18.29

17.65

15.56

15.32

15.10

13.91

11.69