I have large dataset that contains half hourly data with two categories as 'Consumption' and 'Export' both in decimals of three digits. I am trying to split the data into two separate tables for respective categories. I now have the challenge of converting this data from 30 minutes interval to every HOURLY. Can someone help me to to get to this without M language as I am a novice but can work through the menus in Power Query.
I would sincerely appreciate your guidance.
Also, can someone suggest a best book for M and DAX for PowerQuery and PowerBI.
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 🙂
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 🙂
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
