How to solve two dates with the same Status Name

Question

Hi Newbie here,&nbsp;&nbsp;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&nbsp;Vacancy ReferenceCompanyMain RecruiterStatusStatus Start Date14465Company AJBOffer Accepted (On Boarding Forms In Progress )01/02/202214465Company AJBOffer Accepted ( Form Completed )01/02/202214465Company AJBOffer Awaiting Applicant Decision01/02/202214465Company AJBPreparing Candidate Offer31/01/202214465Company AJBOffer Authorising31/01/202214465Company AJBOffer Approved31/01/202214465Company AJBIncomplete30/10/202114465Company AJBPreparing Candidate Offer31/01/202214465Company AJBPipeline Candidate17/11/202114465Company AJBHired01/02/202214465Company AJBHired (no export)17/12/202114465Company AJBNew31/10/202114465Company AJBNew30/10/202114465Company AJBOffer Accepted01/02/202214465Company AJBOffer Accepted16/12/2021&nbsp;&nbsp;&nbsp;

wdx223_daniel · Answer

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

anonymous · Answer

wdx223_Daniel&nbsp;,Hi - Thank you but it seems did not work, it captured the older dates&nbsp;PivotedOriginal

wdx223_daniel · Answer

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

ronrsnfld · Answer

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

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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
&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
&nbsp;
&nbsp;

Forum Discussion

How to solve two dates with the same Status Name

7 Replies

Related content

RAG Status for Due Date

Calculate days spent in the same status - date in same column

solved

Dynamic Status Change for events based on selected date range

HR Headcount by Effective Date and Status

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