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
wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

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

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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

  • 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.