Power query | Deriving dates

[Anonymous]

Hi there,

I have a data set where there are duplicates becuase students were transferred to the same course 2 times.

For reporting I need to pick up the start date from the row where they first started the course. The end date should be picked from the End date coulmn , it should be the maximum date. I tried using min day and max day with grouping. The issue is when the student is active end date should be blank ,but it brings the max date from the group. System is doing what's correct,but I need to the blank value to be returned.

Eg:

Student	Student ID	Start Date	End Date	Course name	Status
John C	ID00083	2/09/2022 0:00	2/01/2023 0:00	Certificate IV in BA 183 N	Transfer to Complete a Related Course
John C	ID00083	3/01/2023 0:00	17/01/2023 0:00	Certificate IV in BA 183 (22)	Completed
John C	ID00083	7/03/2022 0:00	1/09/2022 0:00	Certificate IV in BA 183	Transfer to Complete a Related Course
Amy A	ID09828	2/11/2022 0:00	2/01/2023 0:00	Certificate IV in BA 191 N	Transfer to Complete a Related Course
Amy A	ID09828	24/01/2023 0:00		Certificate IV in BA 191(22)	Active
Amy A	ID09828	6/07/2022 0:00	19/10/2022 0:00	Certificate IV in BA 191	Transfer to Complete a Related Course

John C returns the correct end date as 17/01/2023
Amy A returns 01/01/2023 , but I want it to return blank. How can I do it in power query? Any help is appreciated. Thanks!

[Anonymous]

Hi @Anonymous ,

You can add a custom column that returns 1 if there is a blank in the end date, otherwise 0.

Then group by as follows,

Add a custom column to get the real max date.

Romove unneeded columns and expand [All Rows] to keep original rows.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.