Sort per two dates

[LenkaIvanikova]

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.

[amitchandak]

@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]

@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 · Accepted solution]

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.