Only show last 2 date entries - pq or DAX

[jcastr02]

Hello,
I am trying to achieve something in Power Query or if there is a way in DAX, that's okay too.  Each store # has three different types of support (DE, DR, PCC).  I have a running log start date/end date for each.  Some within the same support may run concurrently.  I am trying to just show the latest two entries.  However, if the end date is blank  (it means it's still active, so I'd like to show that one).  In the below example, see the current log on left, and desired result on right. 

Store	Support Type	Start Date	End Date		Store	Support Type	Start Date	End Date
211	DE	9/25/2023			211	DE	9/25/2023
211	DE	8/11/2020	8/20/2020		211	DE	8/21/2020	9/20/2023
211	DE	5/2/2019	8/10/2020		211	DR	3/6/2017
211	DE	8/21/2020	9/20/2023		211	DR	6/9/2020	9/20/2023
211	DE	3/6/2017	10/4/2018		211	PCC	7/1/2023	7/31/2023
211	DR	3/6/2017			211	PCC	2/1/2024	3/31/2024
211	DR	8/11/2020	8/20/2020		215	DE	12/1/2022
211	DR	6/9/2020	9/20/2023		215	DE	3/2/2021	7/19/2021
211	DR	9/5/2019	6/3/2020		215	DR	12/1/2022
211	PCC	7/1/2023	7/31/2023		215	DR	8/24/2023	12/13/2023
211	PCC	2/1/2023	2/28/2023		215	PCC	2/1/2023	2/28/2023
211	PCC	2/1/2024	3/31/2024		215	PCC	7/1/2023	7/31/2023
215	DE	9/7/2017	10/4/2018
215	DE	12/1/2022
215	DE	4/10/2020	9/2/2020
215	DE	3/2/2021	7/19/2021
215	DR	4/10/2020	7/19/2021
215	DR	9/7/2017	9/2/2020
215	DR	12/1/2022
215	DR	8/24/2023	12/13/2023
215	PCC	2/1/2023	2/28/2023
215	PCC	7/1/2023	7/31/2023

[dufoq3 · Accepted solution]

Hi @jcastr02,

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBDoQgDEWvYlib1LYgsnZmP2FrvP81plBFJOrMhvzQx6f9XRZDiKY3r7ccAcgBDcSiO7P2p+IEiKk4ZE2D6gaS53KPQflrRh4Xo7AZcQsxjMnIixQbm/RUM/HMdG3tZ7cJGiE8NhJzwe0TjcCtzWee5fSAe2weGFsfheiAJKPpEbJ5Ot60Qu7Ykr+LpjC4GVGVTSnaspk8dz1TYVjvUacLqismNj63UNXt1V/xqVldJNk9twRyHZz7J90Dut7T+gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, #"Support Type" = _t, #"Start Date" = _t, #"End Date" = _t]),
    ChangedTypeUS = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}, "en-US"),
    DuplicatedColumn = Table.DuplicateColumn(ChangedTypeUS, "End Date", "End Date Copy"),
    ReplacedValue = Table.ReplaceValue(DuplicatedColumn,null, Date.From(DateTime.LocalNow()),Replacer.ReplaceValue,{"End Date Copy"}),
    GroupedRows = Table.Group(ReplacedValue, {"Store", "Support Type"}, {{"All", each Table.RemoveColumns(Table.MaxN(_, {"End Date Copy"}, 2), {"End Date Copy"}), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[jcastr02]

Thank you so much @dufoq3  would you happen to have the sample BI that you can attach here?

[dufoq3]

You're welcome. It is not necessary to attach pbix file. You can use my query. If you don't know how - read note below my post.