analysis of monthly electricity load profile

[Akhey]

Dear Experts,

i have done some research regarding this topic but I think i can't find proper solution regarding this.

every end of the month i got XLS-Data about energy consumption every quarter of an hour. (an example is --maybe-- attached). All the data are located in one folder. Data extraction using Folder and all are being combined. Some measures are needed to clean the data, i think i got it. Is there any better strategy to load the monthly data to Power Query?

i began to build trust and understanding with PBi and somehow i am not sure whether PBi shows the correct calculation. I do same time-intensive calculation with excel and compare the result with PBi, and the results are different. Only I can make mistakes, excel an PBi can't 😁

The (power) consumption is in kW. to have kWh average of 1 hour data needs to be caculated. And then summed up in the whole month to get the power consumption of a month in (kWh per Month). i will compare it with monthly bill.

im planning to do extended analyses in a extended time range

Date;Consumption;Status
01.01.2019 00:15:00;74,78;OK
01.01.2019 00:30:00;76,25;OK
01.01.2019 00:45:00;75,76;OK
01.01.2019 01:00:00;69,42;OK
01.01.2019 01:15:00;70,16;OK
01.01.2019 01:30:00;75,76;OK
01.01.2019 01:45:00;76;OK
01.01.2019 02:00:00;77,95;OK
01.01.2019 02:15:00;69,91;OK
01.01.2019 02:30:00;69,67;OK
01.01.2019 02:45:00;73,08;OK
01.01.2019 03:00:00;76;OK
01.01.2019 03:15:00;77,46;OK
01.01.2019 03:30:00;75,03;OK
01.01.2019 03:45:00;70,16;OK
01.01.2019 04:00:00;69,42;OK

i would happy to learn from you. thank you i advance!

[Akhey]

.... 5 month later.

i could do a bit more. The whole monthly load data are stored in on folder. 
with aggregation ich can calculate as average from 4 x 15minute data to have Watt-hour. So a daily, weekly, monthly display just a clicks away. 

in combination to other data, such as production data or energy cost, i could see more. we are lucky that the data are stored on share point.

at the same time, we started energy saving measure, and i can see that the load is drop at the same level of productivity. interresting also that i see high load on sunday, where no body a works, by differentiating base load (german: grundlast) and peak load (german: spitzenlast).

i still want to add more such as...

• load live temperature data over XML or GET HTTP.
• periodicaly data refresh
• energy balance

other something usefull feature a guess. a screen shot below in german

[Akhey]

Thanks Richard, very helpfull

i have done it.

but still, i have a lot of question to answer 

best regards

Akhey

[RichardJ · Accepted solution]

Hi @Akhey,

In Power Query Editor click on your Datum/Date column then choose "Add Column' and from the "Time' icon drop down choose 'End of Hour'

This will add a new column with an hourly bucket (e.g all rows with a time within that hour'.

You  can then use the 'Group By' function (use 'Advanced' setting) in Power Query to aggregate By Date and end of hour and perform your 'Sums' Or "averages' accordingly.

it's worth having a play around with those functions.

if you are still stuck please can you upload a sample pbix file.

Thanks,

Richard