Group By & logic for historical view

[jcastr02]

I have a query with three columns  Store, Support Type, and Reporting Month.  I'd like to be able to show a historical time view of the support type by store and shrink the list when the support type stays the same month to month.  I am basically saying that store 162 had support type "Full" from 11/1/2022 thru 5/31/2024 (since there is nothing listed in 6/1/2024 for that store).  Then resumed again starting on 7/1/2024 thru 9/30/2024.  Note that support type full started again on 2/1/2025 but has no end date because the next month (3/1/2025) is not reported out on yet, thus it should stay blank until a different support type shows up next month or the entry under the support type is blank.  I am hoping that makes sense.  I added below a list of current and expected results.  How can I achieve this in power query.

Current

Store	Support Type	Reporting Month
162	Full	11/1/2022
162	Full	12/1/2022
162	Full	1/1/2023
162	Full	2/1/2023
162	Full	3/1/2023
162	Full	4/1/2023
162	Full	5/1/2023
162	Full	6/1/2023
162	Full	7/1/2023
162	Full	8/1/2023
162	Full	9/1/2023
162	Full	10/1/2023
162	Full	11/1/2023
162	Full	1/1/2024
162	Full	2/1/2024
162	Full	3/1/2024
162	Full	4/1/2024
162	Full	5/1/2024
162		6/1/2024
162	Full	7/1/2024
162	Full	8/1/2024
162	Full	9/1/2024
162	TOD9	10/1/2024
162	TOD9	11/1/2024
162	TOD9	12/1/2024
162	TOD9	1/1/2025
162	Full	2/1/2025
178	TOD9	8/1/2024
178	TOD9	9/1/2024
178		10/1/2024
178	TOD9	11/1/2024
178	TOD9	12/1/2024
178	TOD9	1/1/2025
178	TOD9	2/1/2025

Expected

Store	Support Type	Start Date	End Date
162	Full	11/1/2022	5/31/2024
162	Full	7/1/2024	9/30/2024
162	TOD9	10/1/2024	1/31/2025
162	Full	2/1/2025
178	TOD9	8/1/2024	9/30/2024
178	TOD9	11/1/2024

[ZhangKun]

The last value of the End Date column shouldn't appear?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxCoQwEEbhq0hqQTNJTNIvtttsJ97A1vu7oJJo5lVTfAz8vGUxdhLTm3nftv+xdrCDjCJm7d8kTKe4VgTFoXiUgDKhRJSEklHsyMQVLvHYRxGH4lFCK12JozxElISSW/l9P7mKo5GS4CalwU2nBOx2SUzl5zm8ltxK166uP16rHyRMuK2sXg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"Support Type" = _t, #"Reporting Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Store", type text}, {"Support Type", type text}, {"Reporting Month", type date}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", 
            {"Store", "Support Type"}, 
            {
                {"Start Date", each List.Min([Reporting Month])}, 
                {"End Date", each if Table.RowCount(_) = 1 then null else Date.EndOfMonth(List.Max([Reporting Month]))}
            }, 
            GroupKind.Local
        ),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Support Type] <> " "))
in
    #"Filtered Rows"

[lbendlin · Accepted solution]

read about GroupKind.Local

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxCoQwEEbhq0hqQTNJTNIvtttsJ97A1vu7oJJo5lVTfAz8vGUxdhLTm3nftv+xdrCDjCJm7d8kTKe4VgTFoXiUgDKhRJSEklHsyMQVLvHYRxGH4lFCK12JozxElISSW/l9P7mKo5GS4CalwU2nBOx2SUzl5zm8ltxK166uP16rHyRMuK2sXg8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"Support Type" = _t, #"Reporting Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reporting Month", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Store", "Support Type"}, {{"Start Date", each List.Min([Reporting Month]), type nullable date}, {"End Date", each List.Max([Reporting Month]), type nullable date}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Support Type] <> " "))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.