How to Find the Most Current Date From a Column in Power Query - MAX()

4
votes

This is for a Power Query:

I am working on a report that compiles information from different dates and I need a column that generates the most recent date in the list and the previous date to the most current one in separate columns:

Most Current Date must be the same for the whole column (same for Previous Date Column)

Table Name : Skipped_Issue

Worker  |Case   |Report_Date    |MOST_CURRENT_DATE  |PREVIOUS_DATE
Tran    |3000   |1/2018     
Dhni    |52451  |4/2018     
Dhtuni  |39656  |2/2018

enter image description here

2
powerbipowerquerym

2 Answers

10
votes

For the most recent date, you can create a custom column with this formula:

= Date.From(List.Max(NameOfPreviousStep[Report_Date]))

Where NameOfPreviousStep references the prior step in your query (e.g. #"Changed Type" or Source).

To get the second to last date, you can create a custom column that evaluates the max after removing the MOST_CURRENT_DATE

= Date.From(
      List.Max(
          List.RemoveItems(#"Added Custom"[Report_Date],
                           #"Added Custom"[MOST_CURRENT_DATE])))

Here's the whole query for the sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCilKzFPSUTI2MDAAUob6hvpGBoYWSrE60UouGXmZQDFTIxNTQyBtgipXUgqWNbY0MzUD0kZw2VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Worker = _t, Case = _t, Report_Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Worker", type text}, {"Case", Int64.Type}, {"Report_Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "MOST_CURRENT_DATE", each Date.From(List.Max(Source[Report_Date])), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "PREVIOUS_DATE", each Date.From(List.Max(List.RemoveItems(#"Added Custom"[Report_Date], #"Added Custom"[MOST_CURRENT_DATE]))), type date)
in
    #"Added Custom1"
1
votes

@Alexis-Olson That's useful, my respect for lists goes up! I needed to get the max row for each item(worker) date, I wrote code like this:

#"Grouped Rows" = Table.Group(#"Removed Columns", {"Worker"}, {{"AllDates", each _, type table}}), #"Added ReportDate List" = Table.AddColumn(#"Grouped Rows", "ReportDates", each [AllDates][Report_Date]), #"Added MaxReportDate" = Table.AddColumn(#"Added ReportDate List", "Report_Date", each List.Max([ReportDates])), and then merged back to get the single item for the max date for each worker. I'm finding the Grouped Rows with all rows handy when I need a list of a column