• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Grouping by latest date in each month|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Grouping by latest date in each month|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryGrouping by latest date in each mon…
sp_PrintTopic sp_TopicIcon
Grouping by latest date in each month
Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
1
January 20, 2022 - 2:13 am
sp_Permalink sp_Print

Hello, I'm trying to group the Date column in Power Query on the latest date for each month, as displayed in the image below. I'm not sure how to do this, as the Group By function doesn't seem to allow for date calculations. Any help would be greatly appreciated!

 

Illustration.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage Illustration.png (353 KB)
sp_AnswersTopicSeeAnswer See Answer
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 617
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
January 20, 2022 - 8:08 pm
sp_Permalink sp_Print

One way is to create a new column that is the same day for each month (eg first of the month) then group on that to get the max date for each month, then merge that back to the original table to only get the data for the max date of each month - for example:

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Area", type text}, {"Assets", Int64.Type}, {"Criticality Gaps", Int64.Type}}),
AddMonthGroup = Table.AddColumn(ChangedTypes, "MonthGroup", each Date.StartOfMonth([Date])),
GroupMaxDates = Table.RemoveColumns(Table.Group(AddMonthGroup, {"MonthGroup"}, {{"MaxMonthDate", each List.Max([Date]), type datetime}}),{"MonthGroup"}),
FilterMaxMonth = Table.RemoveColumns(Table.NestedJoin(ChangedTypes,{"Date"},GroupMaxDates,{"MaxMonthDate"},"Table1",JoinKind.Inner),{"Table1"})
in
FilterMaxMonth

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
3
January 27, 2022 - 7:09 pm
sp_Permalink sp_Print

Another way (may be longer with some tricks)

first insert the Month name for each date, then group by Month name, max of Date and the entire table,   then expand the table,  check with [Max_Date] = Date,  then filter all the True,  that it

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Area", type text}, {"Assets", Int64.Type}, {"Criticality Gaps", Int64.Type}}),
#"Inserted Month Name" = Table.AddColumn(ChangedTypes, "Month Name", each Date.MonthName([Date]), type text),
#"Grouped Rows" = Table.Group(#"Inserted Month Name", {"Month Name"}, {{"Max_Date", each List.Max([Date]), type date}, {"Data", each _, type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Area", "Assets", "Criticality Gaps"}, {"Date", "Area", "Assets", "Criticality Gaps"}),
#"Added Custom" = Table.AddColumn(#"Expanded Data", "Custom", each [Max_Date]=[Date]),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Month Name", "Max_Date"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
#"Changed Type"

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 441
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
January 31, 2022 - 7:09 pm
sp_Permalink sp_Print

As a variant:

let
    Source = Excel.CurrentWorkbook(){[Name="myData"]}[Content],
    Headers = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    Types = Table.TransformColumnTypes
    (
        Headers,
        {
            {"Date", type date}, 
            {"Area", type text}, 
            {"Assets", Int64.Type}, 
            {"Criticality Gaps", Int64.Type}
        }
    ),
    StartOfMonth = Table.AddColumn(Types, "Start of Month", each Date.StartOfMonth([Date]), type date),
    Sort = Table.Sort(StartOfMonth,{{"Area", Order.Ascending}, {"Date", Order.Descending}}),
    Group = Table.Group
    (
        Sort, {"Area", "Start of Month"},
        {
            {"Group", each _, type table
                [
                    Date=nullable date,
                    Area=nullable text,
                    Assets=nullable number,
                    Criticality Gaps=nullable number,
                    Start of Month=date
                ]
            }
        }
    ),
    RemoveColumns = Table.SelectColumns(Group,{"Group"}),
    AddCustom = Table.AddColumn(RemoveColumns, "Custom", each Table.FirstN ( [Group] , 1 )),
    RemoveColumns1 = Table.SelectColumns(AddCustom,{"Custom"}),
    Expand = Table.ExpandTableColumn
    (
        RemoveColumns1, "Custom",
        {
            "Date", "Area", "Assets", "Criticality Gaps"
        },
        {
            "Date", "Area", "Assets", "Criticality Gaps"
        }
    ),
    Sort1 = Table.Sort
    (
        Expand,
        {
            {"Date", Order.Ascending},
            {"Area", Order.Ascending}
        }
    )
in
    Sort1

sp_AnswersTopicAnswer
Answers Post
Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 132
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
5
January 31, 2022 - 8:02 pm
sp_Permalink sp_Print

Another means is to create a column that is the month number. Then Group By the Month for a Max Date

Duplicate that query and remove the last step

Join the two tables on the max date

AS you can see, there are many options available to solve this

Avatar
Adam Smith
Member
Members
Level 0
Forum Posts: 45
Member Since:
December 18, 2018
sp_UserOfflineSmall Offline
6
February 1, 2022 - 10:59 pm
sp_Permalink sp_Print

Thank you all for your replies. These are all really helpful, and very much appreciated!

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ngoc Tinh
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27248

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.