New column comparing two dates

[makarifgmmh]

Hi! I have a table like the following:

Report Date	Product Code	Production Date
31/08/23	ABC	21/08/23
31/08/23	XYZ	11/08/23
31/08/23	YZX	21/08/23
30/09/23	ABC	20/09/23
30/09/23	XYZ	13/09/23
30/09/23	MNO	05/09/23
30/09/23	EFG	05/09/23

I need to add a column in Power Query. If the product is not in production anymore, I need to mark it as terminated. If the product was not in production I need to mark as New and otherwise existing. Can you please help me to write the code for the new column? Thanks in advance!

Here is the example:

Report Date	Product Code	Production Date	Description
31/08/23	ABC	21/08/23	Existing
31/08/23	XYZ	11/08/23	Existing
31/08/23	YZX	21/08/23	Terminated
30/09/23	ABC	20/09/23	Existing
30/09/23	XYZ	13/09/23	Existing
30/09/23	MNO	05/09/23	New
30/09/23	EFG	05/09/23	New

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjbUN7DQNzJW0lFydHIGkkYwgVgdFNmIyCggaYhDNjIqAkOvgb6BJYrJMAE0WajJxthlff38gaSBKXZZVzd3FNlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Date" = _t, #"Product Code" = _t, #"Production Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"Product Code", type text}, {"Production Date", type date}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product Code"}, {{"Max Date", each List.Max([Report Date]), type nullable date}, {"Min Date", each List.Min([Report Date]), type nullable date}, {"Rows", each _, type table [Report Date=nullable date, Product Code=nullable text, Production Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Description", each if [Max Date]>[Min Date] then "Existing" else
if [Max Date]=List.Max(#"Grouped Rows"[Max Date]) then "New" else "Terminated"),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Custom", "Rows", {"Report Date", "Production Date"}, {"Report Date", "Production Date"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Rows",{"Product Code", "Report Date", "Production Date", "Description"})
in
    #"Removed Other Columns"

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".