Custom gruping / Indexing - based on multiple criterias/variables

Hi micjensen 

You can try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdLdaoMwFAfwVxGvpZyjiSbeWSv9gDnQwgalF86GKhMHsQz2PnuTvdhcdE7HoKbsSk3g5/n4Hw4m4QSYbVrmLorTKLaMMJPNRdRG/PYkX9pzsBeACxtsp/1Az3egfaabBxe99iXM6lxUlTiZR2sORscYdthy+egi/jcWSFm+zqXQHVHAfPimgOrXNcGwx9TE+A2YN8ZgGD922rRLhupf4SbZpvsejEtRNca9KCpRN3lRfbyfhVQD02lZSx4nBryhf/w7Mlq0Mz8/FIDTr/t1Eq2jRLmrrFZjnB/puQr/rUiRXa4b4+3ajk/ZTZVMMkJ+KiFaU2HTQCDtZos9U4j8WZyMsu72xjinpD2/C5L9ttvaLoivO0Nij58=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"DaWinci ID" = _t, Name = _t, #"Transportation date" = _t, ETD = _t, #"Transportation No" = _t, #"Reservation Status" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DaWinci ID", Int64.Type}, {"Name", type text}, {"Transportation date", type date}, {"ETD", type time}, {"Transportation No", type text}, {"Reservation Status", type text}}, "en-GB"),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Flag", each if [Reservation Status] <> "Cancelled" then 1 else 0),
    #"Added Index" = Table.AddIndexColumn(#"Added Conditional Column", "Index", 1, 1, Int64.Type),
    #"Added Conditional Column 2" = Table.AddColumn(#"Added Index", "Row", each if [Flag] = 1 then [Index] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column 2",{"Row"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"DaWinci ID", "Name", "Row"}, {{"All Data", each _, type table [DaWinci ID=nullable number, Name=nullable text, Transportation date=nullable date, ETD=nullable time, Transportation No=nullable text, Reservation Status=nullable text, Flag=number, Index=number, Row=number]}}),
    #"Added Index1" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index1",{"All Data", "Index"}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Removed Other Columns", "All Data", {"DaWinci ID", "Name", "Transportation date", "ETD", "Transportation No", "Reservation Status"}, {"DaWinci ID", "Name", "Transportation date", "ETD", "Transportation No", "Reservation Status"})
in
    #"Expanded All Data"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Thxxx!!!!!! - Great solution, would never figure that out myself.