range of dates when a column changes

[jcastr02]

I have list of stores, but with eff_date (effective date) and end_dt(end date).  How can I achieve to only get range of dates when the DE_Support column Changes.  See desired result table on right side.  

DATA in PQ				Desired Result
str_nbr	eff_dt	end_dt	DE_Support		Str_nbr	eff_dt	end_dt	DE_Support
23	8/24/2023	null	0.15		23	8/24/2023		0.15
23	12/1/2022	8/23/2023	0.45		23	12/1/2022	8/23/2023	0.45
23	10/29/2022	11/30/2022	0.15		23	2/26/2021	11/30/2022	0.15
23	9/8/2022	10/28/2022	0.15		23	1/8/2021	2/25/2021	1
23	2/11/2022	9/7/2022	0.15		23	5/2/2019	1/7/2021	0.15
23	1/28/2022	2/10/2022	0.15		48	10/4/2018	5/1/2019	0.6
23	11/5/2021	1/27/2022	0.15		48	3/6/2017	10/3/2018	0.55
23	9/10/2021	11/4/2021	0.15
23	9/9/2021	9/9/2021	0.15
23	2/26/2021	9/8/2021	0.15
23	1/8/2021	2/25/2021	1
23	12/3/2020	1/7/2021	0.15
23	6/6/2020	12/2/2020	0.15
23	6/4/2020	6/5/2020	0.15
23	3/20/2020	6/3/2020	0.15
23	10/18/2019	3/19/2020	0.15
23	9/5/2019	10/17/2019	0.15
23	5/24/2019	9/4/2019	0.15
23	5/2/2019	5/23/2019	0.15
48	10/4/2018	5/1/2019	0.6
48	6/6/2018	10/3/2018	0.55
48	5/29/2018	6/5/2018	0.55
48	5/3/2018	5/28/2018	0.55
48	1/4/2018	5/2/2018	0.55
48	11/24/2017	1/3/2018	0.55
48	9/22/2017	11/23/2017	0.55
48	9/7/2017	9/21/2017	0.55
48	5/4/2017	9/6/2017	0.55
48	5/4/2017	9/6/2017	0.55
48	3/6/2017	5/3/2017	0.55

[jcastr02]

@lbendlin Thanks so much for your help.  It seems it doesn't capture one of the rows because there is no end date (and that sometimes happens)  see below in yellow.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[jcastr02]

@lbendlinnvmind I was able to do a workaround.  Ty again for solution above! 

[lbendlin · Accepted solution]

Read about GroupKind.Local and its awesome power.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndJJrsMgDADQu2RdxZiZs1S9QfV3vX+NwZiksPmrOPLzIIvn87DueBwZrAdrOP77vN/0MSeG4/XoAC1gBbZhJ9icflYGbBGGCM7Iz7VbgTwUkbxWNHLMLJDWCKdyKtgMpEahZrBVbHqVXo+twkt8V0USU3hf3UZFeY1QE+R1v8vV+dKGdVq3iRCHoT4S35GXRGyn+DV1lCK3RnQhrGtj4Qosa1Z4CKNakeTnqkJ7dpwp4LdIEqG9vCvyuQ3h8swKFUU17VDYuZPYnGFqFNoLxjwutUROh/H7WyCcN7Ibg/0CiQs2O9FCdiDsN0gLliRBFbhGAbyi+H/jNNGvoeb1BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [str_nbr = _t, eff_dt = _t, end_dt = _t, DE_Support = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"str_nbr", Int64.Type}, {"eff_dt", type date}, {"end_dt", type date}, {"DE_Support", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"str_nbr", "DE_Support"}, {{"eff_dt", each List.Min([eff_dt]), type nullable date}, {"end_dt", each List.Max([end_dt]), type nullable date}},GroupKind.Local)
in
    #"Grouped 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 Source step with your own source.