Is it possible to filter to the latest date of each month?

B

bleeet

Board Regular
Joined
May 11, 2009
Messages
208
Office Version
  1. 2013
Platform
  1. Windows
Hi Everyone,


I have a little issue. I have a large data set of project entries and some of them have more than one entry in a month and that only the numbers from the latest day of that month are counted.

so for example this month in the date column. a project has entries from 2021-10-18 and 2021-10-29. I want to be able to filter for only the latest day in each month so the 2021-10-29 entries.

Is this possible? if so how do I do that in power query?
 
Sort by date Sort by votes
Ok, here is the way to do it without manually writing any M, broken down as best as I can.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([PlannedFinishDate])),
    Group = Table.Group(Month, {"Month"}, {{"Max", each List.Max([PlannedFinishDate]), type datetime}, {"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime, Month=number]}}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}),
    IsEqual = Table.AddColumn(Expand, "LastDate", each [Max]=[PlannedFinishDate]),
    Filter = Table.SelectRows(IsEqual, each ([LastDate] = true)),
    RC = Table.RemoveColumns(Filter,{"Month", "Max", "LastDate"})
in
    RC

Step 1:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

This is done automatically when you load the table.

Step 2:

Month = Table.AddColumn(Source, "Month", each Date.Month([PlannedFinishDate])),

Add a custom column with the formula =Date.Month([PlannedFinishDate]), and name the column ‘Month’.

Step 3:

Group = Table.Group(Month, {"Month"}, {{"Max", each List.Max([PlannedFinishDate]), type datetime}, {"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime, Month=number]}})

On the ‘Home’ tab click on ‘Group By’. You will need to open up the advanced options, but in the ‘Group By’ step you’ll want to be creating 2 columns. The first will be the ‘Max’ of the ‘PlannedStartDate’, name that column ‘Max’. The second will be the ‘All Rows’ function and you’ll name that column ‘Data’.

Step 4:

Expand = Table.ExpandTableColumn(Group, "Data", {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"})

In this step your are just expanding the ‘Data’ column.

Step 5:

IsEqual = Table.AddColumn(Expand, "LastDate", each [Max]=[PlannedFinishDate])

Add another custom column named ‘LastDate’ where the formula for the column will be =[Max]=[PlannedFinishDate]

Step 6:

Filter = Table.SelectRows(IsEqual, each ([LastDate] = true))

Go to the ‘LastDate’ column and filter the column to only show true values.

Step 7:

RC = Table.RemoveColumns(Filter,{"Month", "Max", "LastDate"})

Remove the unneeded columns that were created along the way.
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
lrobbo314 said:
Ok, here is the way to do it without manually writing any M, broken down as best as I can.

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([PlannedFinishDate])),
    Group = Table.Group(Month, {"Month"}, {{"Max", each List.Max([PlannedFinishDate]), type datetime}, {"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime, Month=number]}}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}),
    IsEqual = Table.AddColumn(Expand, "LastDate", each [Max]=[PlannedFinishDate]),
    Filter = Table.SelectRows(IsEqual, each ([LastDate] = true)),
    RC = Table.RemoveColumns(Filter,{"Month", "Max", "LastDate"})
in
    RC

Step 1:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

This is done automatically when you load the table.

Step 2:

Month = Table.AddColumn(Source, "Month", each Date.Month([PlannedFinishDate])),

Add a custom column with the formula =Date.Month([PlannedFinishDate]), and name the column ‘Month’.

Step 3:

Group = Table.Group(Month, {"Month"}, {{"Max", each List.Max([PlannedFinishDate]), type datetime}, {"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime, Month=number]}})

On the ‘Home’ tab click on ‘Group By’. You will need to open up the advanced options, but in the ‘Group By’ step you’ll want to be creating 2 columns. The first will be the ‘Max’ of the ‘PlannedStartDate’, name that column ‘Max’. The second will be the ‘All Rows’ function and you’ll name that column ‘Data’.

Step 4:

Expand = Table.ExpandTableColumn(Group, "Data", {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"})

In this step your are just expanding the ‘Data’ column.

Step 5:

IsEqual = Table.AddColumn(Expand, "LastDate", each [Max]=[PlannedFinishDate])

Add another custom column named ‘LastDate’ where the formula for the column will be =[Max]=[PlannedFinishDate]

Step 6:

Filter = Table.SelectRows(IsEqual, each ([LastDate] = true))

Go to the ‘LastDate’ column and filter the column to only show true values.

Step 7:

RC = Table.RemoveColumns(Filter,{"Month", "Max", "LastDate"})

Remove the unneeded columns that were created along the way.
Click to expand...
Thank you so much!!!

I really appreciate your patience with my questions

however I do have a question do you happen to know a good website for people to read and learn M language? If so please share I would like to read it

thanks so much so far this works I will apply it to my large data set and hopefully it will filter the thousands of projects.
 
Upvote 0
bleeet said:
Thank you so much!!!

I really appreciate your patience with my questions

however I do have a question do you happen to know a good website for people to read and learn M language? If so please share I would like to read it

thanks so much so far this works I will apply it to my large data set and hopefully it will filter the thousands of projects.
Click to expand...

I've found the best way to learn is by playing around with PQ, and reading forum posts.

But, I've heard that 'M Is for (Data) Monkey' is a good one.
 
Upvote 0
lrobbo314 said:
I've found the best way to learn is by playing around with PQ, and reading forum posts.

But, I've heard that 'M Is for (Data) Monkey' is a good one.
Click to expand...
apologies @lrobbo314 this did not work :(

for some reason it's skipping some projects by claiming they are false in the max date calculated column when in fact it's supposed to be true

I have been digging in this all night but I don't know why this is happening. I guess if there are more than one project with the same planned start date it will only choose one?

might you have an idea?

Edit: basically for each project I want the filter for the max plannedstartdates in each month. your solution worked for some projects but somehow filtered out other projects due to the custom column calling their max dates false
 
Last edited:
Upvote 0
ah sorry adding another thing

so the projects that do not have a max planned start date of oct 29 2021 are getting filtered out. some of their plannedstartdates are before oct 29 2021. I want to get the latest plannedstartdate of each project in each month and some have oct 25 or oct 1 as their latest date in the plannedstartdate coloumn. (using the month oct as an example this goes for all months )

I hope I am making sense :(
 
Upvote 0
here is a sample for the month of Oct based on the above solution you made project 4404-78 will get filtered out because in the month of Oct the plannedstart date is not max 2021/10/29 it's 2021/10/25

ProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDate
4389-65
3595.1​
2021/10/292021/10/29
4389-65
6772.41​
2021/10/292021/10/29
4389-65
6340.89​
2021/10/292021/10/29
4389-65
760.91​
2021/10/292021/10/29
4389-65
21836.83​
2021/10/182021/10/18
4389-65
10870.6​
2021/10/182021/10/18
4389-65
0​
2021/10/182021/10/18
4389-65
90​
2021/10/292021/10/29
4389-65
25112.35​
2021/10/182021/10/18
4389-65
1288.2​
2021/10/182021/10/18
4389-65
9756.46​
2021/10/182021/10/18
4389-65
5086.7​
2021/10/182021/10/18
4389-65
7861.26​
2021/10/182021/10/18
4389-65
2620.42​
2021/10/182021/10/18
4389-65
10918.41​
2021/10/182021/10/18
4389-65
42389.14​
2021/10/182021/10/18
4404-78
0​
2021/10/25​
2021/10/25​
4404-78
0​
2021/10/25​
2021/10/26​
4404-78
0​
2021/10/25​
2021/10/29​
4404-78
0​
2021/10/25​
2021/10/25​
4404-78
0​
2021/10/25​
2021/10/25​
4404-78
0​
2021/10/25​
2021/10/26​
4404-78
0​
2021/10/25​
2021/10/25​
4404-78
0​
2021/10/25​
2021/10/28​
4404-78
0​
2021/10/25​
2021/10/25​
4404-78
17429.75​
2021/10/25​
2021/10/29​
 
Upvote 0
Like this?

Book1
ABCDEFGHI
1ProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDateProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDate
24389-653595.110/29/202110/29/20214389-653595.110/29/202110/29/2021
34389-656772.4110/29/202110/29/20214389-656772.4110/29/202110/29/2021
44389-656340.8910/29/202110/29/20214389-656340.8910/29/202110/29/2021
54389-65760.9110/29/202110/29/20214389-65760.9110/29/202110/29/2021
64389-6521836.8310/18/202110/18/20214389-659010/29/202110/29/2021
74389-6510870.610/18/202110/18/20214404-78010/25/202110/25/2021
84389-65010/18/202110/18/20214404-78010/25/202110/26/2021
94389-659010/29/202110/29/20214404-78010/25/202110/29/2021
104389-6525112.3510/18/202110/18/20214404-78010/25/202110/25/2021
114389-651288.210/18/202110/18/20214404-78010/25/202110/25/2021
124389-659756.4610/18/202110/18/20214404-78010/25/202110/26/2021
134389-655086.710/18/202110/18/20214404-78010/25/202110/25/2021
144389-657861.2610/18/202110/18/20214404-78010/25/202110/28/2021
154389-652620.4210/18/202110/18/20214404-78010/25/202110/25/2021
164389-6510918.4110/18/202110/18/20214404-7817429.7510/25/202110/29/2021
174389-6542389.1410/18/202110/18/2021
184404-78010/25/202110/25/2021
194404-78010/25/202110/26/2021
204404-78010/25/202110/29/2021
214404-78010/25/202110/25/2021
224404-78010/25/202110/25/2021
234404-78010/25/202110/26/2021
244404-78010/25/202110/25/2021
254404-78010/25/202110/28/2021
264404-78010/25/202110/25/2021
274404-7817429.7510/25/202110/29/2021
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ProjectWorkID"}, {{"MD", each List.Max([PlannedStartDate]), type datetime}, {"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime]}}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}),
    TF = Table.AddColumn(Expand, "Custom", each [MD]=[PlannedStartDate]),
    Filter = Table.SelectRows(TF, each ([Custom] = true)),
    RC = Table.RemoveColumns(Filter,{"MD", "Custom"})
in
    RC
 
Upvote 0
lrobbo314 said:
Like this?

Book1
ABCDEFGHI
1ProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDateProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDate
24389-653595.110/29/202110/29/20214389-653595.110/29/202110/29/2021
34389-656772.4110/29/202110/29/20214389-656772.4110/29/202110/29/2021
44389-656340.8910/29/202110/29/20214389-656340.8910/29/202110/29/2021
54389-65760.9110/29/202110/29/20214389-65760.9110/29/202110/29/2021
64389-6521836.8310/18/202110/18/20214389-659010/29/202110/29/2021
74389-6510870.610/18/202110/18/20214404-78010/25/202110/25/2021
84389-65010/18/202110/18/20214404-78010/25/202110/26/2021
94389-659010/29/202110/29/20214404-78010/25/202110/29/2021
104389-6525112.3510/18/202110/18/20214404-78010/25/202110/25/2021
114389-651288.210/18/202110/18/20214404-78010/25/202110/25/2021
124389-659756.4610/18/202110/18/20214404-78010/25/202110/26/2021
134389-655086.710/18/202110/18/20214404-78010/25/202110/25/2021
144389-657861.2610/18/202110/18/20214404-78010/25/202110/28/2021
154389-652620.4210/18/202110/18/20214404-78010/25/202110/25/2021
164389-6510918.4110/18/202110/18/20214404-7817429.7510/25/202110/29/2021
174389-6542389.1410/18/202110/18/2021
184404-78010/25/202110/25/2021
194404-78010/25/202110/26/2021
204404-78010/25/202110/29/2021
214404-78010/25/202110/25/2021
224404-78010/25/202110/25/2021
234404-78010/25/202110/26/2021
244404-78010/25/202110/25/2021
254404-78010/25/202110/28/2021
264404-78010/25/202110/25/2021
274404-7817429.7510/25/202110/29/2021
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ProjectWorkID"}, {{"MD", each List.Max([PlannedStartDate]), type datetime}, {"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime]}}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}),
    TF = Table.AddColumn(Expand, "Custom", each [MD]=[PlannedStartDate]),
    Filter = Table.SelectRows(TF, each ([Custom] = true)),
    RC = Table.RemoveColumns(Filter,{"MD", "Custom"})
in
    RC
Click to expand...

Sorry for being late to get back to you @lrobbo314 things have been hectic and I could not get time to reach back to you.

thanks but unfortunately this does not work :(

I will need the max date for each project for each month.

so for example as below I am filtering for only the max date for each project in each month so that will be oct 29 and nov 18 and so on

I hope this makes sense and thank you so much for all your help

sorry again for the late reply.


ProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDate
4389-653595.12021/10/292021/10/29
4389-656772.412021/10/292021/10/29
4389-656340.892021/10/292021/10/29
4389-65760.912021/10/292021/10/29
4389-6521836.832021/10/182021/10/18
4389-6510870.62021/10/182021/10/18
4389-6502021/10/182021/10/18
4389-659756.462021/11/152021/10/18
4389-655086.72021/11/152021/10/18
4389-657861.262021/11/182021/10/18
4389-652620.422021/11/182021/10/18
4389-6510918.412021/11/182021/10/18
4389-6542389.142021/11/182021/10/18
 
Upvote 0
Let's give this one a shot.

Book2
ABCDEFGHI
1ProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDateProjectWorkIDPlannedAmountPlannedStartDatePlannedFinishDate
24389-653595.110/29/202110/29/20214389-653595.110/29/202110/29/2021
34389-656772.4110/29/202110/29/20214389-656772.4110/29/202110/29/2021
44389-656340.8910/29/202110/29/20214389-656340.8910/29/202110/29/2021
54389-65760.9110/29/202110/29/20214389-65760.9110/29/202110/29/2021
64389-6521836.8310/18/202110/18/20214389-657861.2611/18/202110/18/2021
74389-6510870.610/18/202110/18/20214389-652620.4211/18/202110/18/2021
84389-65010/18/202110/18/20214389-6510918.4111/18/202110/18/2021
94389-659756.4611/15/202110/18/20214389-6542389.1411/18/202110/18/2021
104389-655086.711/15/202110/18/2021
114389-657861.2611/18/202110/18/2021
124389-652620.4211/18/202110/18/2021
134389-6510918.4111/18/202110/18/2021
144389-6542389.1411/18/202110/18/2021
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Custom", each Date.Month([PlannedStartDate])),
    Group = Table.Group(Month, {"ProjectWorkID", "Custom"}, {{"Count", each _, type table [ProjectWorkID=nullable text, PlannedAmount=nullable number, PlannedStartDate=nullable datetime, PlannedFinishDate=nullable datetime, Custom=number]}}),
    SR = Table.TransformColumns(Group,{{"Count", (tbl)=> 
      let 
        MD = List.Max(tbl[PlannedStartDate]),
        SL = Table.SelectRows(tbl, each [PlannedStartDate]=MD)
      in
        SL
    }}),
    Expand = Table.ExpandTableColumn(SR, "Count", {"PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}),
    RC = Table.RemoveColumns(Expand,{"Custom"})
in
    RC
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
190
Kariba
K
L
  • Solved
Power Query - filter based on cell outside table
Replies
6
Views
783
Peter_SSs
Peter_SSs
A
  • Question Question
date slicer - between
Replies
0
Views
205
ajm
A
A
  • Question Question
PQ Add Dynamic "Average" Column
Replies
1
Views
192
Matt Allington
M
P
  • Question Question
DAX PIVOT: Projects Closure Date Field - Number of Days Change - between differing Forecast Versions
Replies
0
Views
391
phil134
P

Forum statistics

Threads
1,223,692
Messages
6,173,860
Members
452,535
Latest member
berdex

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top