Need help in grouping by month in power query

[burrito]

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.

[AnkitKukreja · Accepted solution]

Hi! @burrito 

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

and you should see your results -

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"