Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
DAnandanHotmail
Regular Visitor

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

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. 

DAnandanHotmail_0-1725767818319.png

I would sincerely appreciate your guidance.

Also, can someone suggest a best book for M and DAX for PowerQuery and PowerBI.

1 ACCEPTED SOLUTION
Claude_Xu
Frequent Visitor

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.

Claude_Xu_0-1725771880839.png

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. 

Claude_Xu_1-1725772097497.png

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

Claude_Xu_2-1725772425847.png

Configure 'Group By' as below

Claude_Xu_3-1725773074445.png

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

Claude_Xu_4-1725773311889.png

Change the configuration of how the newly added column is populated

Claude_Xu_5-1725773464212.png

Change the formula as below

Claude_Xu_6-1725773712870.png

So you get the result as below

Claude_Xu_7-1725773761440.png

 

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 🙂

 

View solution in original post

2 REPLIES 2
Omid_Motamedise
Memorable Member
Memorable Member

see the below link, there are several solultions for your question

 

https://www.linkedin.com/posts/omid-motamedisedeh-74aba166_excelchallenge-powerquerychllenge-excel-a...

 

Claude_Xu
Frequent Visitor

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.

Claude_Xu_0-1725771880839.png

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. 

Claude_Xu_1-1725772097497.png

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

Claude_Xu_2-1725772425847.png

Configure 'Group By' as below

Claude_Xu_3-1725773074445.png

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

Claude_Xu_4-1725773311889.png

Change the configuration of how the newly added column is populated

Claude_Xu_5-1725773464212.png

Change the formula as below

Claude_Xu_6-1725773712870.png

So you get the result as below

Claude_Xu_7-1725773761440.png

 

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

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors