Transforming data in Power Query part of Power BI

[nate17]

Hi I have a csv data source that looks like this:

I have 6 devices capturing 4 types of data at each hour of the day, then repeats every new day by adding 4 new columns across.

What I would like to see is this all unpivoted by Device, Date, Time, L, S, T, E.

I can then link to the relevant master tables for analysis.

Any unpivoting would need to allow for the fact that each day will add new columns.

Any help most appreciated.

[Greg_Deckler]

Please post that as text and then maybe we will have a shot at figuring this out, @ImkeF @edhans 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[nate17]

Does this help? Thanks

nathank	* L=Light(mmol) * S=Soil humidity(%) * T=Temperature(?) * E=EC(?s/cm)
FlowerCare(80:EA:CA:88:D5:99)
Passiflora L13(passiflora caerulea)	9/05/2020				10/05/2020
	L	S	T	E	L	S	T	E
[01:00]	-	-	-	-	2	13	14.3	171
[02:00]	-	-	-	-	2	13	13.7	171
[03:00]	-	-	-	-	2	13	13.2	171
[04:00]	-	-	-	-	2	14	13.5	168
[05:00]	-	-	-	-	3	14	13	169
[06:00]	-	-	-	-	2	14	13.9	166
[07:00]	-	-	-	-	2	13	14.3	164
[08:00]	-	-	-	-	2	13	14.3	164
[09:00]	-	-	-	-	46	13	14.6	162
[10:00]	-	-	-	-	151	4	5.4	53
[11:00]	724	1	25.9	23	748	14	19.8	156
[12:00]	3608	11	27.6	123	4915	14	26.4	141
[13:00]	2462	12	25.6	135	929	15	24.6	147
[14:00]	5431	12	27.7	133	829	13	23.9	150
[15:00]	2791	12	25.8	142	-	-	-	-
[16:00]	662	4	8.1	48	-	-	-	-
[17:00]	1028	13	24	150	-	-	-	-
[18:00]	281	13	20.3	163	-	-	-	-
[19:00]	281	13	20.3	163	-	-	-	-
[20:00]	8	12	16.9	171	-	-	-	-
[21:00]	745655	173	4.9	57	-	-	-	-
[22:00]	3	11	14.6	174	-	-	-	-
[23:00]	3	12	14.9	171	-	-	-	-
[24:00]	3	13	14.6	171	-	-	-	-
FlowerCare(80:EA:CA:88:D5:94)
Passiflora M2(passiflora caerulea)	9/05/2020				10/05/2020
	L	S	T	E	L	S	T	E
[01:00]	-	-	-	-	1	11	14.4	130
[02:00]	-	-	-	-	1	11	13.6	130
[03:00]	-	-	-	-	1	11	13.6	129
[04:00]	-	-	-	-	1	11	13.6	127
[05:00]	-	-	-	-	1	11	13.6	126
[06:00]	-	-	-	-	1	11	14.3	124
[07:00]	-	-	-	-	1	11	14.5	123
[08:00]	-	-	-	-	6	11	14.5	123
[09:00]	-	-	-	-	158	11	15.1	126
[10:00]	-	-	-	-	516	12	16.9	147
[11:00]	137	0	24	0	1944	12	21.9	134
[12:00]	2222	11	26.8	88	4864	12	24.7	133
[13:00]	813	12	24.4	114	910	13	23.1	152
[14:00]	1203	12	25	113	812	12	22.9	154
[15:00]	1853	13	27	120	-	-	-	-
[16:00]	3131	13	27.1	120	-	-	-	-
[17:00]	2137	12	25.7	132	-	-	-	-
[18:00]	433	12	22.5	135	-	-	-	-
[19:00]	12	11	18.3	138	-	-	-	-
[20:00]	1	11	16.3	139	-	-	-	-
[21:00]	1	11	15.2	136	-	-	-	-
[22:00]	1	11	15	134	-	-	-	-
[23:00]	1	11	15.1	131	-	-	-	-
[24:00]	1	11	14.8	130	-	-	-	-
FlowerCare(80:EA:CA:88:D5:9F)
Passiflora D14(passiflora caerulea)	9/05/2020				10/05/2020
	L	S	T	E	L	S	T	E
[01:00]	-	-	-	-	4	11	14.1	112
[02:00]	-	-	-	-	5	11	13.4	110
[03:00]	-	-	-	-	5	11	13.4	108
[04:00]	-	-	-	-	4	11	13.4	105

[edhans]

If you could link to the CSV file that would be better. Put it in DropBox, Google Drive, or OneDrive and share a link.

And explain the data. You want it to handle Device, Date, Time, L, S, T, E. you said, but I don't see columns for all of those.

[nate17]

Hi refer https://www.dropbox.com/sh/pw08fx5sx77rxos/AAD3wVUSokUMzKJgYR998Kb7a?dl=0

I have separated files into 2 tabs - one with original and one with how it should look as a list.

Thanks

[edhans]

Wow. This is ugly. Having a hard time of seeing a path forward. I know it can be done, but hitting on the right combo of grouping, transposing, unpivoting.... bleh.

[edhans]

I've played with this, and I'm not there yet. This is going to require a pretty sophisticated implementation of List.Accumulate to work.