Forum Discussion
New column comparing two dates
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 |
1 Reply
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".
