Forum Discussion
range of dates when a column changes
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 |
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.
4 Replies
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.
