Need Transform Logic

[vengadeshpalani]

Hi All,

I have a table like below INPUT format

INPUT

what is the best way to transfer above table to get OUTPUT format

Output

Please find the attachment for Sample Data.

Thanks

Vengadesh PALANI

[sunny_talwar · Accepted solution]

Try this

Table:

LOAD [Event Start Time],

If([Event Start Time] = Previous([Event Start Time]), RangeSum(Peek('NewTime'), 1/48), 0) as NewTime,

Date(Floor([Event Start Time])) as [Event Date],

Time(Frac([Event Start Time]) + If([Event Start Time] = Previous([Event Start Time]), RangeSum(Peek('NewTime'), 1/48), 0)) as [Event Hour],

    Periods,

    If(IterNo() = 1, [First Half an hour usages], [Second Half an hour usages]) as Usages

FROM

[..\..\Downloads\Temp.xlsx]

(ooxml, embedded labels, table is Sheet2)

While IterNo() <= 2;