Forum Discussion

Daviejoe's avatar
Daviejoe
Icon for Memorable Member rankMemorable Member
5 years ago
Solved

Group by date but retain one column

Hi Power Query Experts

 

I need to group the W Number by the most recent Processed Time Stamp, so I'm fine grouping the W Number by MAX Processed Time Stamp, however I need to retain the Status column and this drops off once I group on Processed Time Stamp.

 

Any help would be great

 

Status ID Number W Number Status Processed Time Stamp
1 9400 370052   2020-09-08T15:00:07.8100000
2 9400 370059   2020-09-08T15:00:07.8270000
3 9400 370091   2020-09-08T15:00:07.8430000
4 9400 370099   2020-09-08T15:00:07.8570000
5 9400 370127   2020-09-08T15:00:07.8570000
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello Daviejoe 

     

    if you only think  you did it 😉 here a possible solution

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc1BCsAgDATAr5SctWyiIcZ39Cb+/xu1F0s9SBfC5jJsa8QUyDMwKhmgMp5jnEAQ4RHlYq1AhZ2F8YR6aCSL850Tmy59nfPO5TRdXtx2T989/TgW++P6DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Status ID" = _t, Number = _t, #"W Number" = _t, Status = _t, #"Processed Time Stamp" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status ID", Int64.Type}, {"Number", Int64.Type}, {"W Number", Int64.Type}, {"Status", type text}, {"Processed Time Stamp", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"W Number"}, {{"AllRows", (tbl)=> Table.SelectRows(tbl, each [Processed Time Stamp]=List.Max(tbl[Processed Time Stamp])){0}[Status]}})
in
    #"Grouped Rows"

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

8 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Daviejoe 

    But the Status column is empty? Also, you want to group by W number but all W numbers in the sample provided are different.

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Daviejoe's avatar
      Daviejoe
      Icon for Memorable Member rankMemorable Member
      5 years ago

      Hey AlB 

       

      I posted a snippet of the information, Status will not always be empty.  It will often contain a one or two letter code which will refer to another dimension table.  If the Status is blank then it means everything is fine.

       

      So I will have multiple rows for the W Number but I only want to retain the latest record.

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hello Daviejoe 

     

    if you only think  you did it 😉 here a possible solution

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc1BCsAgDATAr5SctWyiIcZ39Cb+/xu1F0s9SBfC5jJsa8QUyDMwKhmgMp5jnEAQ4RHlYq1AhZ2F8YR6aCSL850Tmy59nfPO5TRdXtx2T989/TgW++P6DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Status ID" = _t, Number = _t, #"W Number" = _t, Status = _t, #"Processed Time Stamp" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status ID", Int64.Type}, {"Number", Int64.Type}, {"W Number", Int64.Type}, {"Status", type text}, {"Processed Time Stamp", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"W Number"}, {{"AllRows", (tbl)=> Table.SelectRows(tbl, each [Processed Time Stamp]=List.Max(tbl[Processed Time Stamp])){0}[Status]}})
in
    #"Grouped Rows"

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • Daviejoe's avatar
      Daviejoe
      Icon for Memorable Member rankMemorable Member
      5 years ago

      Thanks Jimmy, I'll check that out.

       

      DJ

    • Daviejoe's avatar
      Daviejoe
      Icon for Memorable Member rankMemorable Member
      5 years ago

      I'm getting the below error

       

      'Expression.Error: The name 'Changed Type' wasn't recognized. Make sure it's spelled correctly'

      • Jimmy801's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        5 years ago

        Hello Daviejoe 

         

        when I copy paste my code to a new blank query, the result is this

         

        you have to change my soure-step with your data source or your already created query


        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy