March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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.
Solved! Go to 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"
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
38 | |
34 | |
33 | |
20 |