How to solve two dates with the same Status Name

[jhoyabel19]

Hi Newbie here, 

I have this data and i want to pivot the Status to become the column and the status start date as their values however there are status that has two dates. How do I solve this so once i pivot only one date will be captured and should the be earlier dates

Vacancy Reference	Company	Main Recruiter	Status	Status Start Date
14465	Company A	JB	Offer Accepted (On Boarding Forms In Progress )	01/02/2022
14465	Company A	JB	Offer Accepted ( Form Completed )	01/02/2022
14465	Company A	JB	Offer Awaiting Applicant Decision	01/02/2022
14465	Company A	JB	Preparing Candidate Offer	31/01/2022
14465	Company A	JB	Offer Authorising	31/01/2022
14465	Company A	JB	Offer Approved	31/01/2022
14465	Company A	JB	Incomplete	30/10/2021
14465	Company A	JB	Preparing Candidate Offer	31/01/2022
14465	Company A	JB	Pipeline Candidate	17/11/2021
14465	Company A	JB	Hired	01/02/2022
14465	Company A	JB	Hired (no export)	17/12/2021
14465	Company A	JB	New	31/10/2021
14465	Company A	JB	New	30/10/2021
14465	Company A	JB	Offer Accepted	01/02/2022
14465	Company A	JB	Offer Accepted	16/12/2021

[wdx223_Daniel]

NewStep=Table.Pivot(PreviousStepName,List.Distinct(PrevousStepName[Status]),"Status","Status Start Date",List.Min)

[jhoyabel19]

@wdx223_Daniel ,Hi - Thank you but it seems did not work, it captured the older dates 
Pivoted

Original

[Anonymous]

Hi @jhoyabel19 ,

How about replacing List.Min with List.Max?

NewStep=Table.Pivot(PreviousStepName,List.Distinct(PrevousStepName[Status]),"Status","Status Start Date",List.Max)

Pivoted:

Original:

The whole M codes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZPBasMwDIZfReTUQiFx1nXntmOsg61hg11KD8ZRM0MrG8Vd17efnJWF3ZzCLrGQ9X/SrySbTfaujSZzhlfcISMZzCbZ0h28prNEz9qSXBk+2oAsibegw7H9DUAODnCvA2bbySZT0+nstifAXOKnhTzWO8HD3Bj0AWsYrQkWTnNtqYEHx4cWVgQVu4axbWEsikLlRZmXRVkOI3c4iGV7jIkrWCdtQxxs7v3eynrEIBrbWkcDWBWj1xwxS021rWVF0PHl7kYYKn2eY/hwLP2pGa71nt0n1gOEKzKX5UVRkasiitT/u62sx70l7BGSVHe5UgkDPFruXKa+nq4eRuQAv7zjML70KhN6veDpx1bSai7ViYv8+zlf/R9EN7PezfYb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vacancy Reference", Int64.Type}, {"Company", type text}, {"Main Recruiter", type text}, {"Status", type text}, {"Status Start Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type1", {{"Status Start Date", type date}}, "en-GB"),
    Custom1 = Table.Pivot( #"Changed Type with Locale",List.Distinct( #"Changed Type with Locale"[Status]),"Status","Status Start Date",List.Max)
in
    Custom1

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.           

[ronrsnfld]

You wrote you wanted the earlier date.  Isn't the the same as the older date?

[jhoyabel19]

@ronrsnfld Sorry for the confusion. I want to get the earlier date not the latest date.

[wdx223_Daniel]

the data type of "Status Start Date" is real Date?

[jhoyabel19]

@wdx223_Daniel , Yes DD/MM/YYYY
Thank you for the reply