Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
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 🙂
see the below link, there are several solultions for your question
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
62 | |
21 | |
18 | |
12 |