grouping and maximums

[jcastr02]

Id like to be able to transform within Power Query table to get desired start and end date.  One store will have multiple start and end dates.  Id like for it to give me the maximum start date if a previous record for that store has ended.  Example store 3711 since the end date was already captured in one row, I'd like for table to now only display 10/26/23 as start date and no end date.  The way I'm grouping doesn't seem to work - it's grabbing info from two different rows.

Current		Desired Result
Store	Start Date	End Date		Store	Start Date	End Date
3711	10/26/2023			3711	10/26/2023
3711	10/10/2023	10/13/2023		3812	10/1/2023	10/31/2023
3812	10/1/2023	10/31/2023		5050	12/1/2023
5050	11/1/2023	11/5/2023
5050	12/1/2023

[sevenhills · Accepted solution]

I think, what you need is :

a) First step is get the max start date for each store.

b) Second step is to join back to the original data by both Store and Max Start Date and then get the corresponding End Date.

i.e., You are not looking for max end date, what you are looking is corresponging end date to the max start date of that store.

Let us try this and then you can enhance it to your needs:

Add group by Store, and only add max support start date column, similar to the image.

Next you can do the self merge (join) by these two columns... expand the table and select the end date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi7JL0pV0gHSiUUlCi6JJSCOa14KhBmrE61kbG5oCBQzNNA3MtM3MjAyBnIU0GRAkhAZEMcYwgErsTA0gooiqTA2RKgwNTA1AIkaIqkw1DfFVGCEUAC0PhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Store", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Store"}, {{"Max Support Start Date", each List.Max([Start Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Store", "Max Support Start Date"}, #"Changed Type1", {"Store", "Start Date"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"End Date"}, {"End Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Grouped Rows",{{"End Date", "Max Support End Date"}})
in
    #"Renamed Columns"

input:

output: