Transpose and combining data

[Riddlessaffa]

Hi,
I have a sheet of data with half hourly (i.e. 00:00; 00:30; 01:00 etc) listed in the top row and the date in the fist colum with a kWh value in the corresponding cell. Is there a way I can combine the the date and the half hourly value to make all the date and times be listed in one colum and the corresponding kWh in the next colum?

This is how it currently looks:

Date	00:00	00:30	01:00	01:30	02:00	02:30
01/10/2022	79​	77​	78​	77​	77​	78​
02/10/2022	88​	90​	87​	73​	75​	72​
03/10/2022	88​	89​	82​	75​	92​	88​

This is how I need it to look:

Date Time	kWh
01/10/2022 00:00​	79
01/10/2022 00:30​	77
01/10/2022 01:00​	78
01/10/2022 01:30​	77
01/10/2022 02:00​	77
01/10/2022 02:30​	78

I have a full 2 years worth of data to do. Is there a way to do it using Power Query. I tried but couldn't figure it out.

Thanks

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Riddlessaffa]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks. I'm on Office 365 Version 2311 if that helps. Will update account details now.

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	00:00	00:30	01:00	01:30	02:00	02:30
2	01/10/2022	79	77	78	77	77	78		01/10/2022 00:00	79
3	02/10/2022	88	90	87	73	75	72		01/10/2022 00:30	77
4	03/10/2022	88	89	82	75	92	88		01/10/2022 01:00	78
5									01/10/2022 01:30	77
6									01/10/2022 02:00	77
7									01/10/2022 02:30	78
8									02/10/2022 00:00	88
9									02/10/2022 00:30	90
10									02/10/2022 01:00	87
11									02/10/2022 01:30	73
12									02/10/2022 02:00	75
13									02/10/2022 02:30	72
14									03/10/2022 00:00	88
15									03/10/2022 00:30	89
16									03/10/2022 01:00	82
17									03/10/2022 01:30	75
18									03/10/2022 02:00	92
19									03/10/2022 02:30	88
20
Sheet6

Cell Formulas
Range	Formula
I2:J19	I2	=HSTACK(TOCOL(IF(B1:G1<>"",A2:A4))+TOCOL(IF(A2:A4,B1:G1)),TOCOL(B2:G4))
Dynamic array formulas.

 

[Fluff]

Another option with power query (although it's probably a bit messy)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type time}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Attribute", type number}, {"Date", Int64.Type}}),
    #"Inserted Addition" = Table.AddColumn(#"Changed Type1", "Addition", each [Date] + [Attribute], type number),
    #"Reordered Columns" = Table.ReorderColumns(#"Inserted Addition",{"Addition", "Date", "Attribute", "Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Date", "Attribute"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Addition", type datetime}})
in
    #"Changed Type2"

 

[alansidman]

deleted. Fluff has the answer

 

[lrobbo314]

Just for fun. A bit cleaner version of the PQ solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    DateTime = Table.AddColumn(Unpivot, "DateTime", each DateTime.From(Int64.From([Date]) + Decimal.From(Time.FromText([Attribute])))),
    ROC = Table.TransformColumnTypes(Table.SelectColumns(DateTime,{"DateTime", "Value"}),{{"DateTime", type datetime}, {"Value", Int64.Type}})
in
    ROC

 

[Riddlessaffa]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	00:00	00:30	01:00	01:30	02:00	02:30
2	01/10/2022	79	77	78	77	77	78		01/10/2022 00:00	79
3	02/10/2022	88	90	87	73	75	72		01/10/2022 00:30	77
4	03/10/2022	88	89	82	75	92	88		01/10/2022 01:00	78
5									01/10/2022 01:30	77
6									01/10/2022 02:00	77
7									01/10/2022 02:30	78
8									02/10/2022 00:00	88
9									02/10/2022 00:30	90
10									02/10/2022 01:00	87
11									02/10/2022 01:30	73
12									02/10/2022 02:00	75
13									02/10/2022 02:30	72
14									03/10/2022 00:00	88
15									03/10/2022 00:30	89
16									03/10/2022 01:00	82
17									03/10/2022 01:30	75
18									03/10/2022 02:00	92
19									03/10/2022 02:30	88
20
Sheet6

Cell Formulas
Range	Formula
I2:J19	I2	=HSTACK(TOCOL(IF(B1:G1<>"",A2:A4))+TOCOL(IF(A2:A4,B1:G1)),TOCOL(B2:G4))
Dynamic array formulas.

Thank you Fluff. This will work. I would never have figured that out. Much appreciated.

 

[Riddlessaffa]

Just for fun. A bit cleaner version of the PQ solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"),
    DateTime = Table.AddColumn(Unpivot, "DateTime", each DateTime.From(Int64.From([Date]) + Decimal.From(Time.FromText([Attribute])))),
    ROC = Table.TransformColumnTypes(Table.SelectColumns(DateTime,{"DateTime", "Value"}),{{"DateTime", type datetime}, {"Value", Int64.Type}})
in
    ROC

Thank you. I might give this a try when I'm feeling a bit braver to tackle coding in PQ.

 

[Fluff]

Glad we could help & thanks for the feedback.