How to get Max & Min Dates with respect to other columns in Power Query

[DevadathanK]

Hi Community!

I'm having this doubt in Power Query, can you help me?

I have a table like shown below, Daily status of work items, 

ID Number	State	Date
C01	Open	1-Nov-23
C01	Open	2-Nov-23
C01	Open	3-Nov-23
C01	Closed	4-Nov-23
C01	Closed	5-Nov-23
C01	Closed	6-Nov-23
C02	Open	2-Nov-23

I need to get the Max and Min dates for each State in each ID number, like so -

ID Number	State	Date	Min Date	Max Date
C01	Open	1-Nov-23	1-Nov- 23	3-Nov-23
C01	Open	2-Nov-23	1-Nov-23	3-Nov-23
C01	Open	3-Nov-23	1-Nov-23	3-Nov-23
C01	Closed	4-Nov-23	4-Nov-23	6-Nov-23
C01	Closed	5-Nov-23	4-Nov-23	6-Nov-23
C01	Closed	6-Nov-23	4-Nov-23	6-Nov-23
C02	Open	2-Nov-23	2-Nov-23

I tried using List.Max and other functions, but its just not working for me. Can you help me achieve this?

My idea is that if I can get it in this format, I can then Remove Duplicates (Using ID and State) and remove the normal Date column to calculate the duration of each sate.

ID Number	State	Min Date	Max Date	Duration
C01	Open	1-Nov-23	3-Nov-23	3 Days
C01	Closed	4-Nov-23	6-Nov-23	3 Days

[slorin · Accepted solution]

Hi

let
Source = YourSource,

Group = Table.Group(Source , {"ID Number", "State"}, 
  {{"Data", each _, type table [#"ID Number"=text, State=text, Date=nullable date]},
   {"Min Date", each List.Min([Date]), type nullable date},
   {"Max Date", each List.Max([Date]), type nullable date}}),
Duration = Table.AddColumn(Group, "Duration", each Duration.Days([Max Date] - [Min Date])+1, Int64.Type),
Expand = Table.ExpandTableColumn(Duration, "Data", {"Date"}, {"Date"})
in
Expand

Stéphane

[ChielFaber · Accepted solution]

You can achieve this using mainly the user interface. Use the following steps:

1. Click the group by button.

2. Chose advanced 

3. select two columns in the group by ID number and state like the example below (it's in dutch language):

4. Create a column with the MIN_date using the MIN option from the drop down list (see example below)

5. Create a column with the MAX_date using the MAX option from the drop down list (see example below)

6. Create a custom column called duration with the following code: [Max_date] - [Min_date] (see example below):

7. Change the column type from the duration column to whole number to get the following output:

Optional. If you want to leave the max_date with ID 2 empty you can add an extra step in the custom column calculation MAX_date - MIN_date. Like: 

If  MAX_date - MIN_date = 0 then null else MAX_date - MIN_date

Hope this helps!

[slorin · Accepted solution]

Hi

let
Source = YourSource,

Group = Table.Group(Source , {"ID Number", "State"}, 
  {{"Data", each _, type table [#"ID Number"=text, State=text, Date=nullable date]},
   {"Min Date", each List.Min([Date]), type nullable date},
   {"Max Date", each List.Max([Date]), type nullable date}}),
Duration = Table.AddColumn(Group, "Duration", each Duration.Days([Max Date] - [Min Date])+1, Int64.Type),
Expand = Table.ExpandTableColumn(Duration, "Data", {"Date"}, {"Date"})
in
Expand

Stéphane