Forum Discussion

LenkaIvanikova's avatar
LenkaIvanikova
Regular Visitor
6 years ago
Solved

Sort per two dates

Hello, I need to sort (then remove order duplicates) per two dates. Per requested date ascending, per issued date descending. Is there a way of this "double"sorting in query editor?

 

Thanks,

L.

 

 

 

Need Help
  • dax's avatar
    dax
    6 years ago

    Hi  LenkaIvanikova ， 

    You could refer to my sample for details.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhBtZGBkoG+mj8w0UIrViVYygioywq7IEKzIGKrIGCGDrN5IKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"salse order" = _t, line = _t, req = _t, issue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"salse order", type text}, {"line", Int64.Type}, {"req", type date}, {"issue", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"req", Order.Ascending}, {"id", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"req"}, {{"MAX", each List.Max([id]), type number}, {"ALL", each _, type table [id=number, salse order=text, line=number, req=date, issue=date]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"salse order", "line", "issue"}, {"salse order", "line", "issue"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ALL", each [MAX] = [line])
in
    #"Filtered Rows"

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    LenkaIvanikova ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
    But I doubt there is double sort option
    Appreciate your Kudos.

    • LenkaIvanikova's avatar
      LenkaIvanikova
      Regular Visitor
      6 years ago

      amitchandak  sure. I have Sales Orders with more lines, and each lines has Requested date and Issued Date

      ID        Sales order        Line      Req.Date      Issued Date

      1             A                        1           1.6.2020            10.6.2020

      2             A                        2           1.6.2020            11.6.2020

      3             A                        3           2.6.2020            12.6.2020

       

      I need to remove duplicates, need just one order A, but I need to sort R.D ascending and I.D descending . In this case I need 2.row.

      I know how to table.buffer.sort per date, then remove duplicates....but is it possible to sort 2 dates?

       

      Thanks,

       

      L.

       

      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi  LenkaIvanikova ， 

        You could refer to my sample for details.

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYhBtZGBkoG+mj8w0UIrViVYygioywq7IEKzIGKrIGCGDrN5IKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, #"salse order" = _t, line = _t, req = _t, issue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"salse order", type text}, {"line", Int64.Type}, {"req", type date}, {"issue", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"req", Order.Ascending}, {"id", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"req"}, {{"MAX", each List.Max([id]), type number}, {"ALL", each _, type table [id=number, salse order=text, line=number, req=date, issue=date]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"salse order", "line", "issue"}, {"salse order", "line", "issue"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded ALL", each [MAX] = [line])
in
    #"Filtered Rows"

        Best Regards,
        Zoe Zhi

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.