Summarizing and graphing trends

[rmk9785e]

I was on this forum a few day ago and @alansidman kindly directed me to Power Query. I have been watching the linked videos and learning about it but I am a slow learner at this age. I will appreciate some help in summarizing a dataset to figure out the size of batteries needed for a residential solar system to minimize drawing power from the grid during the year. Data is provided by the hour and I need to summarize it by each day between 'Solar (production) Time' which is 9:00AM-4:59PM these days and changes during the year vs Battery (drain) Time which is 5:00PM-8:59AM these days. Trends can be graphed by the week or the month.
The query may be further refined by using seasonal sunlight hours start and end times.

[dflak]

First of all, I put the data into a table. There are many advantages of using tables. In this case, formulas expressed in terms of column headers which make them easier to understand. Read up on and play with tables. They will save you a lot of headaches in the future.

I added a helper column called time with the formula =IF(AND([@[START TIME]]>=$G$2,[@[END TIME]]<=$I$2),"Solar","Battery") If the start time is greater than or equal to the solar end time AND the end time is less than or equal to the solar end time, then the period is counted under Solar. Else, it is Battery.

Then the formulas at the bottom are: =SUMIFS(Table1[USAGE],Table1[Time],"Solar") and =SUMIFS(Table1[USAGE],Table1[Time],"Battery")