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
burrito
Helper I
Helper I

Need help in grouping by month in power query

Hello, I need help in transforming my data into months with the values in average similar to below. I need this done in power query. Basically, the date will be grouped to per month only and the rate will then be averaged.

burrito_0-1725610404193.png

 

1 ACCEPTED SOLUTION
AnkitKukreja
Super User
Super User

Hi! @burrito 

 

You can open power query editor and use the group by feature in Home tab and then choose these options

AnkitKukreja_0-1725612846005.png

 

and you should see your results -

AnkitKukreja_1-1725612871574.png

 

you can use this m-code for reference - 

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], /*change the source to point out to your source*/
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Rate", Int64.Type}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type", "Date1", each Text.Combine({DateTime.ToText([Date], "MMM"), "-", DateTime.ToText([Date], "yyyy")}), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column",{{"Date1", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date1"}, {{"Month", each List.Average([Rate]), type nullable number}})
in
#"Grouped Rows"

 

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
https://topmate.io/ankit_kukreja

View solution in original post

1 REPLY 1
AnkitKukreja
Super User
Super User

Hi! @burrito 

 

You can open power query editor and use the group by feature in Home tab and then choose these options

AnkitKukreja_0-1725612846005.png

 

and you should see your results -

AnkitKukreja_1-1725612871574.png

 

you can use this m-code for reference - 

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], /*change the source to point out to your source*/
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Rate", Int64.Type}}),
#"Added Custom Column" = Table.AddColumn(#"Changed Type", "Date1", each Text.Combine({DateTime.ToText([Date], "MMM"), "-", DateTime.ToText([Date], "yyyy")}), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column",{{"Date1", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"Date1"}, {{"Month", each List.Average([Rate]), type nullable number}})
in
#"Grouped Rows"

 

For Power BI trainings or support dm or reach out to me on LinkedIn.
If my response has successfully addressed your question or concern, I kindly request that you mark this post as resolved. Additionally, if you found my assistance helpful, a thumbs-up would be greatly appreciated.

Thanks,
Ankit Kukreja
www.linkedin.com/in/ankit-kukreja1904
https://topmate.io/ankit_kukreja

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors