Forum Discussion

makarifgmmh's avatar
makarifgmmh
Regular Visitor
2 years ago

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 ...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago
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".