I have a data set that has Report Date with some fields. I would like to add a column (ex. Status) using excel power query that determines the most recent date (ex. 3/1/2022) and assigns "Current", and the second most recent (ex. 2/1/2022) and assigns "Prior". Any help would be greatly appreciated. Thanks!

CodePudding user response：

Here you go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJSUNJRciwoyEkFMcwVlGJ1UOU8AkCkCUTCCJsmC0w5iCZLiIQxNk2GBpiSEF1gmVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Date" = _t, Company = _t, Number = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"Company", type text}, {"Number", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Company"}, {{"All", each _, type table [Report Date=nullable date, Company=nullable text, Number=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        let a = Table.Sort([All], {{"Report Date", Order.Descending}}),
        b = Table.AddIndexColumn(a, "index"),
        c = Table.AddColumn(b, "Status", each if [index] = 0 then "Current" else if [index] = 1 then "Prior" else null )
        in c
        ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Company", "All"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Report Date", "Company", "Number", "index", "Status"}, {"Report Date", "Company", "Number", "index", "Status"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"index"})
in
    #"Removed Columns1"

CodePudding user response：

In powerquery, try below (which ignores company)

To get all values in the Report Date column use
Table[ReportDate]

Sort it to get it in date order
List.Sort(Table[ReportDate],Order.Descending)

Since there are repeating dates, we remove duplicates
List.Distinct(List.Sort(Table[ReportDate],Order.Descending))

Then if you take the first row, that is the latest date
List.Distinct(List.Sort(Table[ReportDate],Order.Descending)){0}

and the date prior to that one is
List.Distinct(List.Sort(Table[ReportDate],Order.Descending)){1}

Then just add a custom column that compares ReportDate on each row to those

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"Company", type text}, {"Number", Int64.Type}}),
LastDate=List.Distinct(List.Sort(#"Changed Type"[Report Date], Order.Descending)){0},
NextDate=List.Distinct(List.Sort(#"Changed Type"[Report Date], Order.Descending)){1},
#"Added Custom" = Table.AddColumn(#"Changed Type", "Status", each if [Report Date]=LastDate then "Current" else if [Report Date]=NextDate then "Prior" else null)
in  #"Added Custom"