Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to solve two dates with the same Status Name

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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


Original

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

 

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.