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.

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.