Split Data Rows in Two Categories and convert them to hourly report.

Hi, I believe you have realized that using UI cannot fully unleash the power of M, but anyway below is how you can implement the logic primarily with UI. 

Per your description, you have Consumption like below, so let's start from that.

Select 'Time From' column frist and then add a column to extract 'start of the hour'. That way, you can combine records in the same hour into one group in the next step. 

Do 'Group By' operation on 'Date' column and 'Start of Hour' column 

Configure 'Group By' as below

Then add the column for 'Time To'. Select 'Start of Hour' and add a new 'Time Only' column

Change the configuration of how the newly added column is populated

Change the formula as below

So you get the result as below

I'm gonna stop here since renaming columns and reordering columns should not be that difficult to you.

Generally speaking, I suggest to write M code directly instead of using UI if you really wanna master doing ETL with Power Query. 

To your questions about books, I don't think there is one 'best' book, and I don't know if you have any programming experience. If you know how to program, especially if you have much experice on functional programming, learning M should be quite easy to you. Probably you don't even need a comprehensive book. Just going through some contents on https://learn.microsoft.com/en-us/powerquery-m/ may be sufficient. However, if you enjoy reading books, below two are recommended by quite a few people

- ISBN: 1835089720

- ISBN: 1509306978

My two cents 🙂