Fetching multiple rows from Table with Condition

[MBS]

Hi All,
I want to fetch multiple records(Rows actually) from table. Condition to fetch the row is that date should be EndOfMonth in Date Column.
EndOfMonth Date could be more than one time. we need to get the last one. I tried it with additional query with end of month date. then merge tow queries with the help of index column and then sorting. But could not get the result. So seeking help in this.
Please suggest.

Input					Output
Date	Heading-1	Heading-2	Value		Date	Heading-1	Heading-2	Value
01-01-2023​	A1	A2	1​		31-01-2023​	A1	A2	3​
05-01-2023​	B1	B2	2​		28-02-2023​	A1	A2	5​
31-01-2023​	A1	A2	3​		31-03-2023​	A1	A2	9​
02-02-2023​	B1	B2	4​
28-02-2023​	A1	A2	5​
01-03-2023​	B1	B2	6​
15-03-2023​	A1	A2	7​
31-03-2023​	B1	B2	8​
31-03-2023​	A1	A2	9​

 

[p45cal]

In the attached, in the yellow cells some single-celled formulae.
For these to work, it's important that the dates in column A are real dates (not text).
The simplest looking formula is in cell F15, which has been derived from the formula in cell F6. You can use either.

It was harder than I thought it would be, and I might have made heavier weather of it than I needed to; hopefully someone can come up with a more elegant solution.

ps. It'd be nice if you said thanks a bit more, we're not chatgpt.

 

[MBS]

In the attached, in the yellow cells some single-celled formulae.
For these to work, it's important that the dates in column A are real dates (not text).
The simplest looking formula is in cell F15, which has been derived from the formula in cell F6. You can use either.

It was harder than I thought it would be, and I might have made heavier weather of it than I needed to; hopefully someone can come up with a more elegant solution.

ps. It'd be nice if you said thanks a bit more, we're not chatgpt.

Thanks a lot @ p45cal.
I always learn some cool tricks every time you reply to post.
I am sorry that, I forgot to mention in post that I like to get the desired solution with power query.
I would be be great for me , if you can suggest the solution in power query.

Thank you so much.

​

 

[p45cal]

Attach the workbook concerned.

 

[Chihiro]

Simple method:
Add "Flag" column:

= if [Date]=Date.EndOfMonth([Date]) then true else false

Filter for = true.

Remove flag column.

Sort ascending on Value.

Group by Date, All rows.

Keep last row of grouped column.

Expand all but the Date column. Drop unnecessary columns and change data type as needed.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Heading-1", type text}, {"Heading-2", type text}, {"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag", each if [Date]=Date.EndOfMonth([Date]) then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Flag] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Flag"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Date"}, {{"Temp", each _, type table [Date=nullable date, #"Heading-1"=nullable text, #"Heading-2"=nullable text, Value=nullable text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Last([Temp])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom1", "Custom", {"Heading-1", "Heading-2", "Value"}, {"Heading-1", "Heading-2", "Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Temp"})
in
    #"Removed Columns1"

 

[MBS]

Simple method:
Add "Flag" column:

= if [Date]=Date.EndOfMonth([Date]) then true else false

Filter for = true.

Remove flag column.

Sort ascending on Value.

Group by Date, All rows.

Keep last row of grouped column.

Expand all but the Date column. Drop unnecessary columns and change data type as needed.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Heading-1", type text}, {"Heading-2", type text}, {"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag", each if [Date]=Date.EndOfMonth([Date]) then true else false),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Flag] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Flag"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Date"}, {{"Temp", each _, type table [Date=nullable date, #"Heading-1"=nullable text, #"Heading-2"=nullable text, Value=nullable text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Last([Temp])),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom1", "Custom", {"Heading-1", "Heading-2", "Value"}, {"Heading-1", "Heading-2", "Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Temp"})
in
    #"Removed Columns1"

Thank you so much @ Chihiro, I got the solution.

I have one doubt. Say for one or more Dates in Date Column are not exactly the End Of Month Date. And / Or have more than one occurrence.
say, 28-04-23 and 28-04-2023 have occurred and we want to pick the last row with 28-04-23. I am stuck to pick the last one only

 

[Chihiro]

Please give sample that's representative of your actual need. Otherwise, we won't be able to help.
I'd recommend uploading sample workbook, with manually crafted desired output.