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

[bleeet]

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?

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E
1	Date	Value		Date	Value
2	10/26/2021	51		10/26/2021	51
3	11/10/2021	88		11/25/2021	97
4	11/25/2021	97		12/25/2021	48
5	12/10/2021	77		1/24/2022	92
6	12/25/2021	48		2/23/2022	96
7	1/9/2022	15		3/25/2022	21
8	1/24/2022	92		4/24/2022	15
9	2/8/2022	57		5/24/2022	61
10	2/23/2022	96		6/23/2022	69
11	3/10/2022	50		7/23/2022	78
12	3/25/2022	21		8/7/2022	18
13	4/9/2022	75
14	4/24/2022	15
15	5/9/2022	87
16	5/24/2022	61
17	6/8/2022	95
18	6/23/2022	69
19	7/8/2022	8
20	7/23/2022	78
21	8/7/2022	18
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([Date])),
    Group = Table.Group(Month, {"Month"}, {{"Count", each _, type table [Date=datetime, Value=number, Month=number]}}),
    SR = Table.TransformColumns(Group,{{"Count", (tbl)=>
        let 
            MD = List.Max(tbl[Date]),
            Sel = Table.SelectRows(tbl, each _[Date]=MD)
        in
            Sel
    }}),
    Expand = Table.ExpandTableColumn(SR, "Count", {"Date", "Value"}, {"Date", "Value"}),
    RC = Table.RemoveColumns(Expand,{"Month"})
in
    RC

 

[bleeet]

How about this?

Book1
	A	B	C	D	E
1	Date	Value		Date	Value
2	10/26/2021	51		10/26/2021	51
3	11/10/2021	88		11/25/2021	97
4	11/25/2021	97		12/25/2021	48
5	12/10/2021	77		1/24/2022	92
6	12/25/2021	48		2/23/2022	96
7	1/9/2022	15		3/25/2022	21
8	1/24/2022	92		4/24/2022	15
9	2/8/2022	57		5/24/2022	61
10	2/23/2022	96		6/23/2022	69
11	3/10/2022	50		7/23/2022	78
12	3/25/2022	21		8/7/2022	18
13	4/9/2022	75
14	4/24/2022	15
15	5/9/2022	87
16	5/24/2022	61
17	6/8/2022	95
18	6/23/2022	69
19	7/8/2022	8
20	7/23/2022	78
21	8/7/2022	18
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([Date])),
    Group = Table.Group(Month, {"Month"}, {{"Count", each _, type table [Date=datetime, Value=number, Month=number]}}),
    SR = Table.TransformColumns(Group,{{"Count", (tbl)=>
        let
            MD = List.Max(tbl[Date]),
            Sel = Table.SelectRows(tbl, each _[Date]=MD)
        in
            Sel
    }}),
    Expand = Table.ExpandTableColumn(SR, "Count", {"Date", "Value"}, {"Date", "Value"}),
    RC = Table.RemoveColumns(Expand,{"Month"})
in
    RC

thanks but I have no idea how to add this to my query

might there be a way without using M language?

 

[lrobbo314]

I'm not sure what you mean. Everything in PowerQuery is driven by M. It might be useful if you would post a sample of your data using the XL2BB add-in provided by this forum. It would also be useful if you would update your profile so we know what version of Excel you're working with.

Having said that, here are a couple more ways

Here is another way using PQ that can be done just using the tools in the ribbon and not having to write any M manually.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Value		Date	Value		Date	Value		Date	Value
2	10/26/2021	51		10/26/2021	51		10/26/2021	51		10/26/2021	51
3	11/10/2021	88		11/25/2021	97		11/25/2021	97		11/10/2021	88
4	11/25/2021	97		12/25/2021	48		12/25/2021	48		12/10/2021	77
5	12/10/2021	77		1/24/2022	92		1/24/2022	92		1/9/2022	15
6	12/25/2021	48		2/23/2022	96		2/23/2022	96		2/8/2022	57
7	1/9/2022	15		3/25/2022	21		3/25/2022	21		3/10/2022	50
8	1/24/2022	92		4/24/2022	15		4/24/2022	15		4/9/2022	75
9	2/8/2022	57		5/24/2022	61		5/24/2022	61		5/9/2022	87
10	2/23/2022	96		6/23/2022	69		6/23/2022	69		6/8/2022	95
11	3/10/2022	50		7/23/2022	78		7/23/2022	78		7/8/2022	8
12	3/25/2022	21		8/7/2022	18		8/7/2022	18		8/7/2022	18
13	4/9/2022	75
14	4/24/2022	15
15	5/9/2022	87
16	5/24/2022	61
17	6/8/2022	95
18	6/23/2022	69
19	7/8/2022	8
20	7/23/2022	78
21	8/7/2022	18
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([Date])),
    Group = Table.Group(Month, {"Month"}, {{"Last", each List.Max([Date]), type datetime}, {"Data", each _, type table [Date=datetime, Value=number, Month=number]}}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"Date", "Value"}, {"Date", "Value"}),
    TF = Table.AddColumn(Expand, "Custom", each [Last]=[Date]),
    Filter = Table.SelectRows(TF, each ([Custom] = true)),
    RC = Table.RemoveColumns(Filter,{"Month", "Last", "Custom"})
in
    RC

And here is a way using VBA if you'd rather.

Sub BLEET()
Dim AR() As Variant:        AR = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
Dim r As Range:             Set r = Range("L1")

With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(AR)
        If Not .exists(Month(AR(i, 1))) Then
            .Add Month(AR(i, 1)), Join(Array(AR(i, 1), AR(i, 2)), ",")
        Else
            If Split(.Item(Month(AR(i, 1))), ",")(0) < AR(i, 1) Then .Item(Month(AR(i, 1))) = Join(Array(AR(i, 1), AR(i, 2)), ",")
        End If
    Next i
    Set r = Range("L2").Resize(.Count)
    r.Value2 = Application.Transpose(.items)
    r.TextToColumns DataType:=xlDelimited, Comma:=True
End With
End Sub

 

[bleeet]

I'm not sure what you mean. Everything in PowerQuery is driven by M. It might be useful if you would post a sample of your data using the XL2BB add-in provided by this forum. It would also be useful if you would update your profile so we know what version of Excel you're working with.

Having said that, here are a couple more ways

Here is another way using PQ that can be done just using the tools in the ribbon and not having to write any M manually.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Value		Date	Value		Date	Value		Date	Value
2	10/26/2021	51		10/26/2021	51		10/26/2021	51		10/26/2021	51
3	11/10/2021	88		11/25/2021	97		11/25/2021	97		11/10/2021	88
4	11/25/2021	97		12/25/2021	48		12/25/2021	48		12/10/2021	77
5	12/10/2021	77		1/24/2022	92		1/24/2022	92		1/9/2022	15
6	12/25/2021	48		2/23/2022	96		2/23/2022	96		2/8/2022	57
7	1/9/2022	15		3/25/2022	21		3/25/2022	21		3/10/2022	50
8	1/24/2022	92		4/24/2022	15		4/24/2022	15		4/9/2022	75
9	2/8/2022	57		5/24/2022	61		5/24/2022	61		5/9/2022	87
10	2/23/2022	96		6/23/2022	69		6/23/2022	69		6/8/2022	95
11	3/10/2022	50		7/23/2022	78		7/23/2022	78		7/8/2022	8
12	3/25/2022	21		8/7/2022	18		8/7/2022	18		8/7/2022	18
13	4/9/2022	75
14	4/24/2022	15
15	5/9/2022	87
16	5/24/2022	61
17	6/8/2022	95
18	6/23/2022	69
19	7/8/2022	8
20	7/23/2022	78
21	8/7/2022	18
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([Date])),
    Group = Table.Group(Month, {"Month"}, {{"Last", each List.Max([Date]), type datetime}, {"Data", each _, type table [Date=datetime, Value=number, Month=number]}}),
    Expand = Table.ExpandTableColumn(Group, "Data", {"Date", "Value"}, {"Date", "Value"}),
    TF = Table.AddColumn(Expand, "Custom", each [Last]=[Date]),
    Filter = Table.SelectRows(TF, each ([Custom] = true)),
    RC = Table.RemoveColumns(Filter,{"Month", "Last", "Custom"})
in
    RC

And here is a way using VBA if you'd rather.

Sub BLEET()
Dim AR() As Variant:        AR = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
Dim r As Range:             Set r = Range("L1")

With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(AR)
        If Not .exists(Month(AR(i, 1))) Then
            .Add Month(AR(i, 1)), Join(Array(AR(i, 1), AR(i, 2)), ",")
        Else
            If Split(.Item(Month(AR(i, 1))), ",")(0) < AR(i, 1) Then .Item(Month(AR(i, 1))) = Join(Array(AR(i, 1), AR(i, 2)), ",")
        End If
    Next i
    Set r = Range("L2").Resize(.Count)
    r.Value2 = Application.Transpose(.items)
    r.TextToColumns DataType:=xlDelimited, Comma:=True
End With
End Sub

I mean that I use the ribbon to do most of the stuff and do not write the code to do things in power query.

I am a little lost reading the code

apologies I am using excel 2013

 

[lrobbo314]

I mean that I use the ribbon to do most of the stuff and do not write the code to do things in power query.

This is what the second PQ code was doing.

I added a custom column with the formula Date.Month([Date])
Grouped on the month column, with one aggregation being Max of date (Column named 'Last'), and the other being All Rows.
Added another custom column that checks if [Date]=[Last]
Then filter that custom columns to only show True.
Then finally just removing the unneeded columns.

 

[bleeet]

This is what the second PQ code was doing.

I added a custom column with the formula Date.Month([Date])
Grouped on the month column, with one aggregation being Max of date (Column named 'Last'), and the other being All Rows.
Added another custom column that checks if [Date]=[Last]
Then filter that custom columns to only show True.
Then finally just removing the unneeded columns.

thanks but I believe I did not describe the issue right or I maybe I am doing this wrong because I am getting the last date for each project which is sometime next year. I want the last date in each month

I attached a sample set for one project which is the example I mentioned I want to grab the latest date in each month so oct 29 in this sample data. only the planned amount in that is going to be counted. Sorry I cannot add the L2bb is blocked on my end. I am want filter the plannnedstartdate to the latest date of each month. this project and entries for each month throughout the year. The ProjectWorkID is the project name

ProjectWorkID	PlannedAmount	PlannedStartDate	PlannedFinishDate
4389-65	39280.52​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	1714.94​	2021/10/18	2021/10/18
4389-65	7861.26​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	3595.1​	2021/10/29	2021/10/29
4389-65	6772.41​	2021/10/29	2021/10/29
4389-65	697.19​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	685.9​	2021/10/18	2021/10/18
4389-65	21194.57​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	2620.42​	2021/10/18	2021/10/18
4389-65	5809.92​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	5809.92​	2021/10/18	2021/10/18
4389-65	2870.38​	2021/10/18	2021/10/18
4389-65	2543.35​	2021/10/18	2021/10/18
4389-65	6340.89​	2021/10/29	2021/10/29
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	13288.8​	2021/10/18	2021/10/18
4389-65	-4544.42​	2021/10/18	2021/10/18
4389-65	-1488.35​	2021/10/18	2021/10/18
4389-65	1610.6​	2021/10/18	2021/10/18
4389-65	21194.57​	2021/10/18	2021/10/18
4389-65	869.83​	2021/10/18	2021/10/18
4389-65	685.9​	2021/10/18	2021/10/18
4389-65	17718.4​	2021/10/18	2021/10/18
4389-65	91560.55​	2021/10/18	2021/10/18
4389-65	2543.35​	2021/10/18	2021/10/18
4389-65	760.91​	2021/10/29	2021/10/29
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	740​	2021/10/29	2021/10/29
4389-65	1507.87​	2021/10/18	2021/10/18
4389-65	5715.81​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	12565.6​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	-321.48​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	-538.91​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	5809.92​	2021/10/18	2021/10/18
4389-65	6809.69​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	348.6​	2021/10/18	2021/10/18
4389-65	5715.81​	2021/10/18	2021/10/18
4389-65	685.9​	2021/10/18	2021/10/18
4389-65	-545.33​	2021/10/18	2021/10/18
4389-65	2870.38​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	23919.84​	2021/10/18	2021/10/18
4389-65	5809.92​	2021/10/18	2021/10/18
4389-65	1610.6​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	-1503.23​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	3271.35​	2021/10/18	2021/10/18
4389-65	2126.21​	2021/10/18	2021/10/18
4389-65	-641.57​	2021/10/18	2021/10/18
4389-65	685.9​	2021/10/18	2021/10/18
4389-65	2391.98​	2021/10/18	2021/10/18
4389-65	-180.39​	2021/10/18	2021/10/18
4389-65	56436.74​	2021/10/29	2021/10/29
4389-65	0​	2021/10/18	2021/10/18
4389-65	23919.84​	2021/10/18	2021/10/18
4389-65	10918.41​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	-2315.07​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	65510.5​	2021/10/18	2021/10/18
4389-65	-321.48​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	4713.66​	2021/10/18	2021/10/18
4389-65	5728.26​	2021/10/18	2021/10/18
4389-65	4713.66​	2021/10/18	2021/10/18
4389-65	1521.81​	2021/10/18	2021/10/18
4389-65	39280.52​	2021/10/18	2021/10/18
4389-65	23919.84​	2021/10/18	2021/10/18
4389-65	-180.39​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	629​	2021/10/29	2021/10/29
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	12681.79​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	13421.69​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	5715.81​	2021/10/18	2021/10/18
4389-65	697.19​	2021/10/18	2021/10/18
4389-65	23919.84​	2021/10/18	2021/10/18
4389-65	65510.49​	2021/10/18	2021/10/18
4389-65	7861.26​	2021/10/18	2021/10/18
4389-65	48041.02​	2021/10/18	2021/10/18
4389-65	2908.62​	2021/10/18	2021/10/18
4389-65	-277.81​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	-277.81​	2021/10/18	2021/10/18
4389-65	1304.47​	2021/10/18	2021/10/18
4389-65	3271.35​	2021/10/18	2021/10/18
4389-65	-2315.07​	2021/10/18	2021/10/18
4389-65	2870.38​	2021/10/18	2021/10/18
4389-65	697.19​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	65510.5​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	47735.52​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	5764.92​	2021/10/18	2021/10/18
4389-65	5809.92​	2021/10/18	2021/10/18
4389-65	817.16​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	1594.66​	2021/10/18	2021/10/18
4389-65	84778.29​	2021/10/18	2021/10/18
4389-65	1610.6​	2021/10/18	2021/10/18
4389-65	14291.13​	2021/10/18	2021/10/18
4389-65	3013.48​	2021/10/18	2021/10/18
4389-65	869.83​	2021/10/18	2021/10/18
4389-65	23919.84​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	-1503.23​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	2904.96​	2021/10/18	2021/10/18
4389-65	65510.49​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	13421.69​	2021/10/18	2021/10/18
4389-65	7248.57​	2021/10/18	2021/10/18
4389-65	685.9​	2021/10/18	2021/10/18
4389-65	392.56​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	10987.27​	2021/10/18	2021/10/18
4389-65	14291.13​	2021/10/18	2021/10/18
4389-65	1610.6​	2021/10/18	2021/10/18
4389-65	10735​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	5308.74​	2021/10/18	2021/10/18
4389-65	685.9​	2021/10/18	2021/10/18
4389-65	23919.84​	2021/10/18	2021/10/18
4389-65	7248.57​	2021/10/18	2021/10/18
4389-65	24238.5​	2021/10/18	2021/10/18
4389-65	5243​	2021/10/29	2021/10/29
4389-65	392.56​	2021/10/18	2021/10/18
4389-65	21836.83​	2021/10/18	2021/10/18
4389-65	20532.95​	2021/10/29	2021/10/29
4389-65	697.19​	2021/10/18	2021/10/18
4389-65	697.19​	2021/10/18	2021/10/18
4389-65	7861.26​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	685.9​	2021/10/18	2021/10/18
4389-65	2463.95​	2021/10/29	2021/10/29
4389-65	5715.81​	2021/10/18	2021/10/18
4389-65	23919.84​	2021/10/18	2021/10/18
4389-65	1041.41​	2021/10/18	2021/10/18
4389-65	19933.2​	2021/10/18	2021/10/18
4389-65	2870.38​	2021/10/18	2021/10/18
4389-65	-2679.02​	2021/10/18	2021/10/18
4389-65	5715.81​	2021/10/18	2021/10/18
4389-65	81303.84​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	10173.39​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	2870.38​	2021/10/18	2021/10/18
4389-65	5715.81​	2021/10/18	2021/10/18
4389-65	-4490.96​	2021/10/18	2021/10/18
4389-65	-267.9​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	-5346.38​	2021/10/18	2021/10/18
4389-65	13421.69​	2021/10/18	2021/10/18
4389-65	2870.38​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	1310.21​	2021/10/18	2021/10/18
4389-65	1714.94​	2021/10/18	2021/10/18
4389-65	1310.21​	2021/10/18	2021/10/18
4389-65	5715.81​	2021/10/18	2021/10/18
4389-65	-2679.02​	2021/10/18	2021/10/18
4389-65	13421.69​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	431.41​	2021/10/29	2021/10/29
4389-65	44239.5​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	21836.83​	2021/10/18	2021/10/18
4389-65	10870.6​	2021/10/18	2021/10/18
4389-65	0​	2021/10/18	2021/10/18
4389-65	90​	2021/10/29	2021/10/29
4389-65	25112.35​	2021/10/18	2021/10/18
4389-65	1288.2​	2021/10/18	2021/10/18
4389-65	9756.46​	2021/10/18	2021/10/18
4389-65	5086.7​	2021/10/18	2021/10/18
4389-65	7861.26​	2021/10/18	2021/10/18
4389-65	2620.42​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18
4389-65	-2232.52​	2021/10/18	2021/10/18
4389-65	2870.38​	2021/10/18	2021/10/18
4389-65	-178.6​	2021/10/18	2021/10/18
4389-65	8678.4​	2021/10/18	2021/10/18
4389-65	10918.41​	2021/10/18	2021/10/18
4389-65	42389.14​	2021/10/18	2021/10/18

​

 

[bleeet]

also there are hundreds of different projects in the database.

 

[lrobbo314]

Like this?

Book1
	A	B	C	D	E	F	G	H	I
1	ProjectWorkID	PlannedAmount	PlannedStartDate	PlannedFinishDate		ProjectWorkID	PlannedAmount	PlannedStartDate	PlannedFinishDate
2	4389-65	39280.52	10/18/2021	10/18/2021		4389-65	3595.1	10/29/2021	10/29/2021
3	4389-65	0	10/18/2021	10/18/2021		4389-65	6772.41	10/29/2021	10/29/2021
4	4389-65	5086.7	10/18/2021	10/18/2021		4389-65	6340.89	10/29/2021	10/29/2021
5	4389-65	1714.94	10/18/2021	10/18/2021		4389-65	760.91	10/29/2021	10/29/2021
6	4389-65	7861.26	10/18/2021	10/18/2021		4389-65	740	10/29/2021	10/29/2021
7	4389-65	42389.14	10/18/2021	10/18/2021		4389-65	56436.74	10/29/2021	10/29/2021
8	4389-65	3595.1	10/29/2021	10/29/2021		4389-65	629	10/29/2021	10/29/2021
9	4389-65	6772.41	10/29/2021	10/29/2021		4389-65	5243	10/29/2021	10/29/2021
10	4389-65	697.19	10/18/2021	10/18/2021		4389-65	20532.95	10/29/2021	10/29/2021
11	4389-65	0	10/18/2021	10/18/2021		4389-65	2463.95	10/29/2021	10/29/2021
12	4389-65	685.9	10/18/2021	10/18/2021		4389-65	431.41	10/29/2021	10/29/2021
13	4389-65	21194.57	10/18/2021	10/18/2021		4389-65	90	10/29/2021	10/29/2021
14	4389-65	42389.14	10/18/2021	10/18/2021
15	4389-65	42389.14	10/18/2021	10/18/2021
16	4389-65	2620.42	10/18/2021	10/18/2021
17	4389-65	5809.92	10/18/2021	10/18/2021
18	4389-65	0	10/18/2021	10/18/2021
19	4389-65	5809.92	10/18/2021	10/18/2021
20	4389-65	2870.38	10/18/2021	10/18/2021
21	4389-65	2543.35	10/18/2021	10/18/2021
22	4389-65	6340.89	10/29/2021	10/29/2021
23	4389-65	5086.7	10/18/2021	10/18/2021
24	4389-65	13288.8	10/18/2021	10/18/2021
25	4389-65	-4544.42	10/18/2021	10/18/2021
26	4389-65	-1488.35	10/18/2021	10/18/2021
27	4389-65	1610.6	10/18/2021	10/18/2021
28	4389-65	21194.57	10/18/2021	10/18/2021
29	4389-65	869.83	10/18/2021	10/18/2021
30	4389-65	685.9	10/18/2021	10/18/2021
31	4389-65	17718.4	10/18/2021	10/18/2021
32	4389-65	91560.55	10/18/2021	10/18/2021
33	4389-65	2543.35	10/18/2021	10/18/2021
34	4389-65	760.91	10/29/2021	10/29/2021
35	4389-65	42389.14	10/18/2021	10/18/2021
36	4389-65	0	10/18/2021	10/18/2021
37	4389-65	740	10/29/2021	10/29/2021
38	4389-65	1507.87	10/18/2021	10/18/2021
39	4389-65	5715.81	10/18/2021	10/18/2021
40	4389-65	0	10/18/2021	10/18/2021
41	4389-65	12565.6	10/18/2021	10/18/2021
42	4389-65	0	10/18/2021	10/18/2021
43	4389-65	-321.48	10/18/2021	10/18/2021
44	4389-65	42389.14	10/18/2021	10/18/2021
45	4389-65	-538.91	10/18/2021	10/18/2021
46	4389-65	0	10/18/2021	10/18/2021
47	4389-65	5809.92	10/18/2021	10/18/2021
48	4389-65	6809.69	10/18/2021	10/18/2021
49	4389-65	5086.7	10/18/2021	10/18/2021
50	4389-65	348.6	10/18/2021	10/18/2021
51	4389-65	5715.81	10/18/2021	10/18/2021
52	4389-65	685.9	10/18/2021	10/18/2021
53	4389-65	-545.33	10/18/2021	10/18/2021
54	4389-65	2870.38	10/18/2021	10/18/2021
55	4389-65	0	10/18/2021	10/18/2021
56	4389-65	0	10/18/2021	10/18/2021
57	4389-65	23919.84	10/18/2021	10/18/2021
58	4389-65	5809.92	10/18/2021	10/18/2021
59	4389-65	1610.6	10/18/2021	10/18/2021
60	4389-65	0	10/18/2021	10/18/2021
61	4389-65	-1503.23	10/18/2021	10/18/2021
62	4389-65	42389.14	10/18/2021	10/18/2021
63	4389-65	3271.35	10/18/2021	10/18/2021
64	4389-65	2126.21	10/18/2021	10/18/2021
65	4389-65	-641.57	10/18/2021	10/18/2021
66	4389-65	685.9	10/18/2021	10/18/2021
67	4389-65	2391.98	10/18/2021	10/18/2021
68	4389-65	-180.39	10/18/2021	10/18/2021
69	4389-65	56436.74	10/29/2021	10/29/2021
70	4389-65	0	10/18/2021	10/18/2021
71	4389-65	23919.84	10/18/2021	10/18/2021
72	4389-65	10918.41	10/18/2021	10/18/2021
73	4389-65	42389.14	10/18/2021	10/18/2021
74	4389-65	-2315.07	10/18/2021	10/18/2021
75	4389-65	0	10/18/2021	10/18/2021
76	4389-65	65510.5	10/18/2021	10/18/2021
77	4389-65	-321.48	10/18/2021	10/18/2021
78	4389-65	0	10/18/2021	10/18/2021
79	4389-65	4713.66	10/18/2021	10/18/2021
80	4389-65	5728.26	10/18/2021	10/18/2021
81	4389-65	4713.66	10/18/2021	10/18/2021
82	4389-65	1521.81	10/18/2021	10/18/2021
83	4389-65	39280.52	10/18/2021	10/18/2021
84	4389-65	23919.84	10/18/2021	10/18/2021
85	4389-65	-180.39	10/18/2021	10/18/2021
86	4389-65	0	10/18/2021	10/18/2021
87	4389-65	629	10/29/2021	10/29/2021
88	4389-65	5086.7	10/18/2021	10/18/2021
89	4389-65	12681.79	10/18/2021	10/18/2021
90	4389-65	0	10/18/2021	10/18/2021
91	4389-65	13421.69	10/18/2021	10/18/2021
92	4389-65	5086.7	10/18/2021	10/18/2021
93	4389-65	5715.81	10/18/2021	10/18/2021
94	4389-65	697.19	10/18/2021	10/18/2021
95	4389-65	23919.84	10/18/2021	10/18/2021
96	4389-65	65510.49	10/18/2021	10/18/2021
97	4389-65	7861.26	10/18/2021	10/18/2021
98	4389-65	48041.02	10/18/2021	10/18/2021
99	4389-65	2908.62	10/18/2021	10/18/2021
100	4389-65	-277.81	10/18/2021	10/18/2021
101	4389-65	0	10/18/2021	10/18/2021
102	4389-65	-277.81	10/18/2021	10/18/2021
103	4389-65	1304.47	10/18/2021	10/18/2021
104	4389-65	3271.35	10/18/2021	10/18/2021
105	4389-65	-2315.07	10/18/2021	10/18/2021
106	4389-65	2870.38	10/18/2021	10/18/2021
107	4389-65	697.19	10/18/2021	10/18/2021
108	4389-65	0	10/18/2021	10/18/2021
109	4389-65	65510.5	10/18/2021	10/18/2021
110	4389-65	5086.7	10/18/2021	10/18/2021
111	4389-65	47735.52	10/18/2021	10/18/2021
112	4389-65	0	10/18/2021	10/18/2021
113	4389-65	5764.92	10/18/2021	10/18/2021
114	4389-65	5809.92	10/18/2021	10/18/2021
115	4389-65	817.16	10/18/2021	10/18/2021
116	4389-65	5086.7	10/18/2021	10/18/2021
117	4389-65	1594.66	10/18/2021	10/18/2021
118	4389-65	84778.29	10/18/2021	10/18/2021
119	4389-65	1610.6	10/18/2021	10/18/2021
120	4389-65	14291.13	10/18/2021	10/18/2021
121	4389-65	3013.48	10/18/2021	10/18/2021
122	4389-65	869.83	10/18/2021	10/18/2021
123	4389-65	23919.84	10/18/2021	10/18/2021
124	4389-65	0	10/18/2021	10/18/2021
125	4389-65	-1503.23	10/18/2021	10/18/2021
126	4389-65	0	10/18/2021	10/18/2021
127	4389-65	2904.96	10/18/2021	10/18/2021
128	4389-65	65510.49	10/18/2021	10/18/2021
129	4389-65	5086.7	10/18/2021	10/18/2021
130	4389-65	0	10/18/2021	10/18/2021
131	4389-65	13421.69	10/18/2021	10/18/2021
132	4389-65	7248.57	10/18/2021	10/18/2021
133	4389-65	685.9	10/18/2021	10/18/2021
134	4389-65	392.56	10/18/2021	10/18/2021
135	4389-65	42389.14	10/18/2021	10/18/2021
136	4389-65	10987.27	10/18/2021	10/18/2021
137	4389-65	14291.13	10/18/2021	10/18/2021
138	4389-65	1610.6	10/18/2021	10/18/2021
139	4389-65	10735	10/18/2021	10/18/2021
140	4389-65	42389.14	10/18/2021	10/18/2021
141	4389-65	5308.74	10/18/2021	10/18/2021
142	4389-65	685.9	10/18/2021	10/18/2021
143	4389-65	23919.84	10/18/2021	10/18/2021
144	4389-65	7248.57	10/18/2021	10/18/2021
145	4389-65	24238.5	10/18/2021	10/18/2021
146	4389-65	5243	10/29/2021	10/29/2021
147	4389-65	392.56	10/18/2021	10/18/2021
148	4389-65	21836.83	10/18/2021	10/18/2021
149	4389-65	20532.95	10/29/2021	10/29/2021
150	4389-65	697.19	10/18/2021	10/18/2021
151	4389-65	697.19	10/18/2021	10/18/2021
152	4389-65	7861.26	10/18/2021	10/18/2021
153	4389-65	0	10/18/2021	10/18/2021
154	4389-65	685.9	10/18/2021	10/18/2021
155	4389-65	2463.95	10/29/2021	10/29/2021
156	4389-65	5715.81	10/18/2021	10/18/2021
157	4389-65	23919.84	10/18/2021	10/18/2021
158	4389-65	1041.41	10/18/2021	10/18/2021
159	4389-65	19933.2	10/18/2021	10/18/2021
160	4389-65	2870.38	10/18/2021	10/18/2021
161	4389-65	-2679.02	10/18/2021	10/18/2021
162	4389-65	5715.81	10/18/2021	10/18/2021
163	4389-65	81303.84	10/18/2021	10/18/2021
164	4389-65	42389.14	10/18/2021	10/18/2021
165	4389-65	10173.39	10/18/2021	10/18/2021
166	4389-65	5086.7	10/18/2021	10/18/2021
167	4389-65	0	10/18/2021	10/18/2021
168	4389-65	2870.38	10/18/2021	10/18/2021
169	4389-65	5715.81	10/18/2021	10/18/2021
170	4389-65	-4490.96	10/18/2021	10/18/2021
171	4389-65	-267.9	10/18/2021	10/18/2021
172	4389-65	5086.7	10/18/2021	10/18/2021
173	4389-65	-5346.38	10/18/2021	10/18/2021
174	4389-65	13421.69	10/18/2021	10/18/2021
175	4389-65	2870.38	10/18/2021	10/18/2021
176	4389-65	42389.14	10/18/2021	10/18/2021
177	4389-65	1310.21	10/18/2021	10/18/2021
178	4389-65	1714.94	10/18/2021	10/18/2021
179	4389-65	1310.21	10/18/2021	10/18/2021
180	4389-65	5715.81	10/18/2021	10/18/2021
181	4389-65	-2679.02	10/18/2021	10/18/2021
182	4389-65	13421.69	10/18/2021	10/18/2021
183	4389-65	0	10/18/2021	10/18/2021
184	4389-65	5086.7	10/18/2021	10/18/2021
185	4389-65	431.41	10/29/2021	10/29/2021
186	4389-65	44239.5	10/18/2021	10/18/2021
187	4389-65	5086.7	10/18/2021	10/18/2021
188	4389-65	0	10/18/2021	10/18/2021
189	4389-65	21836.83	10/18/2021	10/18/2021
190	4389-65	10870.6	10/18/2021	10/18/2021
191	4389-65	0	10/18/2021	10/18/2021
192	4389-65	90	10/29/2021	10/29/2021
193	4389-65	25112.35	10/18/2021	10/18/2021
194	4389-65	1288.2	10/18/2021	10/18/2021
195	4389-65	9756.46	10/18/2021	10/18/2021
196	4389-65	5086.7	10/18/2021	10/18/2021
197	4389-65	7861.26	10/18/2021	10/18/2021
198	4389-65	2620.42	10/18/2021	10/18/2021
199	4389-65	42389.14	10/18/2021	10/18/2021
200	4389-65	-2232.52	10/18/2021	10/18/2021
201	4389-65	2870.38	10/18/2021	10/18/2021
202	4389-65	-178.6	10/18/2021	10/18/2021
203	4389-65	8678.4	10/18/2021	10/18/2021
204	4389-65	10918.41	10/18/2021	10/18/2021
205	4389-65	42389.14	10/18/2021	10/18/2021
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([PlannedFinishDate])),
    Group = Table.Group(Month, {"Month"}, {{"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime, Month=number]}}),
    SR = Table.TransformColumns(Group,{{"Data", (tbl)=>
        let 
            MD = List.Max(tbl[PlannedFinishDate]),
            Sel = Table.SelectRows(tbl, each _[PlannedFinishDate]=MD)
        in
            Sel
    }}),
    Expand = Table.ExpandTableColumn(SR, "Data", {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}),
    RC = Table.RemoveColumns(Expand,{"Month"})
in
    RC

 

[bleeet]

Like this?

Book1
	A	B	C	D	E	F	G	H	I
1	ProjectWorkID	PlannedAmount	PlannedStartDate	PlannedFinishDate		ProjectWorkID	PlannedAmount	PlannedStartDate	PlannedFinishDate
2	4389-65	39280.52	10/18/2021	10/18/2021		4389-65	3595.1	10/29/2021	10/29/2021
3	4389-65	0	10/18/2021	10/18/2021		4389-65	6772.41	10/29/2021	10/29/2021
4	4389-65	5086.7	10/18/2021	10/18/2021		4389-65	6340.89	10/29/2021	10/29/2021
5	4389-65	1714.94	10/18/2021	10/18/2021		4389-65	760.91	10/29/2021	10/29/2021
6	4389-65	7861.26	10/18/2021	10/18/2021		4389-65	740	10/29/2021	10/29/2021
7	4389-65	42389.14	10/18/2021	10/18/2021		4389-65	56436.74	10/29/2021	10/29/2021
8	4389-65	3595.1	10/29/2021	10/29/2021		4389-65	629	10/29/2021	10/29/2021
9	4389-65	6772.41	10/29/2021	10/29/2021		4389-65	5243	10/29/2021	10/29/2021
10	4389-65	697.19	10/18/2021	10/18/2021		4389-65	20532.95	10/29/2021	10/29/2021
11	4389-65	0	10/18/2021	10/18/2021		4389-65	2463.95	10/29/2021	10/29/2021
12	4389-65	685.9	10/18/2021	10/18/2021		4389-65	431.41	10/29/2021	10/29/2021
13	4389-65	21194.57	10/18/2021	10/18/2021		4389-65	90	10/29/2021	10/29/2021
14	4389-65	42389.14	10/18/2021	10/18/2021
15	4389-65	42389.14	10/18/2021	10/18/2021
16	4389-65	2620.42	10/18/2021	10/18/2021
17	4389-65	5809.92	10/18/2021	10/18/2021
18	4389-65	0	10/18/2021	10/18/2021
19	4389-65	5809.92	10/18/2021	10/18/2021
20	4389-65	2870.38	10/18/2021	10/18/2021
21	4389-65	2543.35	10/18/2021	10/18/2021
22	4389-65	6340.89	10/29/2021	10/29/2021
23	4389-65	5086.7	10/18/2021	10/18/2021
24	4389-65	13288.8	10/18/2021	10/18/2021
25	4389-65	-4544.42	10/18/2021	10/18/2021
26	4389-65	-1488.35	10/18/2021	10/18/2021
27	4389-65	1610.6	10/18/2021	10/18/2021
28	4389-65	21194.57	10/18/2021	10/18/2021
29	4389-65	869.83	10/18/2021	10/18/2021
30	4389-65	685.9	10/18/2021	10/18/2021
31	4389-65	17718.4	10/18/2021	10/18/2021
32	4389-65	91560.55	10/18/2021	10/18/2021
33	4389-65	2543.35	10/18/2021	10/18/2021
34	4389-65	760.91	10/29/2021	10/29/2021
35	4389-65	42389.14	10/18/2021	10/18/2021
36	4389-65	0	10/18/2021	10/18/2021
37	4389-65	740	10/29/2021	10/29/2021
38	4389-65	1507.87	10/18/2021	10/18/2021
39	4389-65	5715.81	10/18/2021	10/18/2021
40	4389-65	0	10/18/2021	10/18/2021
41	4389-65	12565.6	10/18/2021	10/18/2021
42	4389-65	0	10/18/2021	10/18/2021
43	4389-65	-321.48	10/18/2021	10/18/2021
44	4389-65	42389.14	10/18/2021	10/18/2021
45	4389-65	-538.91	10/18/2021	10/18/2021
46	4389-65	0	10/18/2021	10/18/2021
47	4389-65	5809.92	10/18/2021	10/18/2021
48	4389-65	6809.69	10/18/2021	10/18/2021
49	4389-65	5086.7	10/18/2021	10/18/2021
50	4389-65	348.6	10/18/2021	10/18/2021
51	4389-65	5715.81	10/18/2021	10/18/2021
52	4389-65	685.9	10/18/2021	10/18/2021
53	4389-65	-545.33	10/18/2021	10/18/2021
54	4389-65	2870.38	10/18/2021	10/18/2021
55	4389-65	0	10/18/2021	10/18/2021
56	4389-65	0	10/18/2021	10/18/2021
57	4389-65	23919.84	10/18/2021	10/18/2021
58	4389-65	5809.92	10/18/2021	10/18/2021
59	4389-65	1610.6	10/18/2021	10/18/2021
60	4389-65	0	10/18/2021	10/18/2021
61	4389-65	-1503.23	10/18/2021	10/18/2021
62	4389-65	42389.14	10/18/2021	10/18/2021
63	4389-65	3271.35	10/18/2021	10/18/2021
64	4389-65	2126.21	10/18/2021	10/18/2021
65	4389-65	-641.57	10/18/2021	10/18/2021
66	4389-65	685.9	10/18/2021	10/18/2021
67	4389-65	2391.98	10/18/2021	10/18/2021
68	4389-65	-180.39	10/18/2021	10/18/2021
69	4389-65	56436.74	10/29/2021	10/29/2021
70	4389-65	0	10/18/2021	10/18/2021
71	4389-65	23919.84	10/18/2021	10/18/2021
72	4389-65	10918.41	10/18/2021	10/18/2021
73	4389-65	42389.14	10/18/2021	10/18/2021
74	4389-65	-2315.07	10/18/2021	10/18/2021
75	4389-65	0	10/18/2021	10/18/2021
76	4389-65	65510.5	10/18/2021	10/18/2021
77	4389-65	-321.48	10/18/2021	10/18/2021
78	4389-65	0	10/18/2021	10/18/2021
79	4389-65	4713.66	10/18/2021	10/18/2021
80	4389-65	5728.26	10/18/2021	10/18/2021
81	4389-65	4713.66	10/18/2021	10/18/2021
82	4389-65	1521.81	10/18/2021	10/18/2021
83	4389-65	39280.52	10/18/2021	10/18/2021
84	4389-65	23919.84	10/18/2021	10/18/2021
85	4389-65	-180.39	10/18/2021	10/18/2021
86	4389-65	0	10/18/2021	10/18/2021
87	4389-65	629	10/29/2021	10/29/2021
88	4389-65	5086.7	10/18/2021	10/18/2021
89	4389-65	12681.79	10/18/2021	10/18/2021
90	4389-65	0	10/18/2021	10/18/2021
91	4389-65	13421.69	10/18/2021	10/18/2021
92	4389-65	5086.7	10/18/2021	10/18/2021
93	4389-65	5715.81	10/18/2021	10/18/2021
94	4389-65	697.19	10/18/2021	10/18/2021
95	4389-65	23919.84	10/18/2021	10/18/2021
96	4389-65	65510.49	10/18/2021	10/18/2021
97	4389-65	7861.26	10/18/2021	10/18/2021
98	4389-65	48041.02	10/18/2021	10/18/2021
99	4389-65	2908.62	10/18/2021	10/18/2021
100	4389-65	-277.81	10/18/2021	10/18/2021
101	4389-65	0	10/18/2021	10/18/2021
102	4389-65	-277.81	10/18/2021	10/18/2021
103	4389-65	1304.47	10/18/2021	10/18/2021
104	4389-65	3271.35	10/18/2021	10/18/2021
105	4389-65	-2315.07	10/18/2021	10/18/2021
106	4389-65	2870.38	10/18/2021	10/18/2021
107	4389-65	697.19	10/18/2021	10/18/2021
108	4389-65	0	10/18/2021	10/18/2021
109	4389-65	65510.5	10/18/2021	10/18/2021
110	4389-65	5086.7	10/18/2021	10/18/2021
111	4389-65	47735.52	10/18/2021	10/18/2021
112	4389-65	0	10/18/2021	10/18/2021
113	4389-65	5764.92	10/18/2021	10/18/2021
114	4389-65	5809.92	10/18/2021	10/18/2021
115	4389-65	817.16	10/18/2021	10/18/2021
116	4389-65	5086.7	10/18/2021	10/18/2021
117	4389-65	1594.66	10/18/2021	10/18/2021
118	4389-65	84778.29	10/18/2021	10/18/2021
119	4389-65	1610.6	10/18/2021	10/18/2021
120	4389-65	14291.13	10/18/2021	10/18/2021
121	4389-65	3013.48	10/18/2021	10/18/2021
122	4389-65	869.83	10/18/2021	10/18/2021
123	4389-65	23919.84	10/18/2021	10/18/2021
124	4389-65	0	10/18/2021	10/18/2021
125	4389-65	-1503.23	10/18/2021	10/18/2021
126	4389-65	0	10/18/2021	10/18/2021
127	4389-65	2904.96	10/18/2021	10/18/2021
128	4389-65	65510.49	10/18/2021	10/18/2021
129	4389-65	5086.7	10/18/2021	10/18/2021
130	4389-65	0	10/18/2021	10/18/2021
131	4389-65	13421.69	10/18/2021	10/18/2021
132	4389-65	7248.57	10/18/2021	10/18/2021
133	4389-65	685.9	10/18/2021	10/18/2021
134	4389-65	392.56	10/18/2021	10/18/2021
135	4389-65	42389.14	10/18/2021	10/18/2021
136	4389-65	10987.27	10/18/2021	10/18/2021
137	4389-65	14291.13	10/18/2021	10/18/2021
138	4389-65	1610.6	10/18/2021	10/18/2021
139	4389-65	10735	10/18/2021	10/18/2021
140	4389-65	42389.14	10/18/2021	10/18/2021
141	4389-65	5308.74	10/18/2021	10/18/2021
142	4389-65	685.9	10/18/2021	10/18/2021
143	4389-65	23919.84	10/18/2021	10/18/2021
144	4389-65	7248.57	10/18/2021	10/18/2021
145	4389-65	24238.5	10/18/2021	10/18/2021
146	4389-65	5243	10/29/2021	10/29/2021
147	4389-65	392.56	10/18/2021	10/18/2021
148	4389-65	21836.83	10/18/2021	10/18/2021
149	4389-65	20532.95	10/29/2021	10/29/2021
150	4389-65	697.19	10/18/2021	10/18/2021
151	4389-65	697.19	10/18/2021	10/18/2021
152	4389-65	7861.26	10/18/2021	10/18/2021
153	4389-65	0	10/18/2021	10/18/2021
154	4389-65	685.9	10/18/2021	10/18/2021
155	4389-65	2463.95	10/29/2021	10/29/2021
156	4389-65	5715.81	10/18/2021	10/18/2021
157	4389-65	23919.84	10/18/2021	10/18/2021
158	4389-65	1041.41	10/18/2021	10/18/2021
159	4389-65	19933.2	10/18/2021	10/18/2021
160	4389-65	2870.38	10/18/2021	10/18/2021
161	4389-65	-2679.02	10/18/2021	10/18/2021
162	4389-65	5715.81	10/18/2021	10/18/2021
163	4389-65	81303.84	10/18/2021	10/18/2021
164	4389-65	42389.14	10/18/2021	10/18/2021
165	4389-65	10173.39	10/18/2021	10/18/2021
166	4389-65	5086.7	10/18/2021	10/18/2021
167	4389-65	0	10/18/2021	10/18/2021
168	4389-65	2870.38	10/18/2021	10/18/2021
169	4389-65	5715.81	10/18/2021	10/18/2021
170	4389-65	-4490.96	10/18/2021	10/18/2021
171	4389-65	-267.9	10/18/2021	10/18/2021
172	4389-65	5086.7	10/18/2021	10/18/2021
173	4389-65	-5346.38	10/18/2021	10/18/2021
174	4389-65	13421.69	10/18/2021	10/18/2021
175	4389-65	2870.38	10/18/2021	10/18/2021
176	4389-65	42389.14	10/18/2021	10/18/2021
177	4389-65	1310.21	10/18/2021	10/18/2021
178	4389-65	1714.94	10/18/2021	10/18/2021
179	4389-65	1310.21	10/18/2021	10/18/2021
180	4389-65	5715.81	10/18/2021	10/18/2021
181	4389-65	-2679.02	10/18/2021	10/18/2021
182	4389-65	13421.69	10/18/2021	10/18/2021
183	4389-65	0	10/18/2021	10/18/2021
184	4389-65	5086.7	10/18/2021	10/18/2021
185	4389-65	431.41	10/29/2021	10/29/2021
186	4389-65	44239.5	10/18/2021	10/18/2021
187	4389-65	5086.7	10/18/2021	10/18/2021
188	4389-65	0	10/18/2021	10/18/2021
189	4389-65	21836.83	10/18/2021	10/18/2021
190	4389-65	10870.6	10/18/2021	10/18/2021
191	4389-65	0	10/18/2021	10/18/2021
192	4389-65	90	10/29/2021	10/29/2021
193	4389-65	25112.35	10/18/2021	10/18/2021
194	4389-65	1288.2	10/18/2021	10/18/2021
195	4389-65	9756.46	10/18/2021	10/18/2021
196	4389-65	5086.7	10/18/2021	10/18/2021
197	4389-65	7861.26	10/18/2021	10/18/2021
198	4389-65	2620.42	10/18/2021	10/18/2021
199	4389-65	42389.14	10/18/2021	10/18/2021
200	4389-65	-2232.52	10/18/2021	10/18/2021
201	4389-65	2870.38	10/18/2021	10/18/2021
202	4389-65	-178.6	10/18/2021	10/18/2021
203	4389-65	8678.4	10/18/2021	10/18/2021
204	4389-65	10918.41	10/18/2021	10/18/2021
205	4389-65	42389.14	10/18/2021	10/18/2021
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Source, "Month", each Date.Month([PlannedFinishDate])),
    Group = Table.Group(Month, {"Month"}, {{"Data", each _, type table [ProjectWorkID=text, PlannedAmount=text, PlannedStartDate=datetime, PlannedFinishDate=datetime, Month=number]}}),
    SR = Table.TransformColumns(Group,{{"Data", (tbl)=>
        let
            MD = List.Max(tbl[PlannedFinishDate]),
            Sel = Table.SelectRows(tbl, each _[PlannedFinishDate]=MD)
        in
            Sel
    }}),
    Expand = Table.ExpandTableColumn(SR, "Data", {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}, {"ProjectWorkID", "PlannedAmount", "PlannedStartDate", "PlannedFinishDate"}),
    RC = Table.RemoveColumns(Expand,{"Month"})
in
    RC

Yes so that I can get the latest date for each month for each project

sorry can you explain what you did so I can try it ?