Custom gruping / Indexing - based on multiple criterias/variables

[micjensen]

Hi community

I'm trying to make a custom grouping/indexing based on various criterias.

I have a table containing data for people and their helicopter flights/departures. A Scheduled flight can be cancelled and the reservation status will then be "Cancelled" and a new scheduled time will be created for that person --> a new row is then created in the table.

I'm really lost on how to do this, when there is so many different variables that has an impact on the result. All tricks, advices and any sort of help would be highly appreciated, thanks a lot in advance.

I want to group/index the table in the following way:

DaWinci ID	Name	Transportation date	ETD	Transportation No	Reservation Status
494082	JENSEN, Carsten Nybro	02.01.2023	17:30	SHW617	Cancelled
494082	JENSEN, Carsten Nybro	05.01.2023	11:30	BBX611	Cancelled
494082	JENSEN, Carsten Nybro	05.01.2023	11:30	BBX611	Arrived
494082	JENSEN, Carsten Nybro	16.01.2023	08:00	BBX605	Cancelled
494082	JENSEN, Carsten Nybro	16.01.2023	18:00	SHW619	Cancelled
494082	JENSEN, Carsten Nybro	17.01.2023	10:30	SHW1619	Arrived
498100	CHRISTENSEN, Niels Oehlenschläger	11.01.2023	08:00	BBX605	Cancelled
498100	CHRISTENSEN, Niels Oehlenschläger	12.01.2023	07:00	SHW1617	Cancelled
498100	CHRISTENSEN, Niels Oehlenschläger	13.01.2023	11:30	BBX611	Cancelled
500950	GREGERSEN, Dan	10.01.2023	17:30	SHW617	Cancelled
500950	GREGERSEN, Dan	10.01.2023	19:30	SHW617	Created
500950	GREGERSEN, Dan	17.01.2023	23:58	SHW617	Cancelled
500950	GREGERSEN, Dan	17.01.2023	14:30	SHW641	Cancelled
500950	GREGERSEN, Dan	18.01.2023	08:15	BBX1641	Checked in
489954	MARTINSEN, JAN	18.01.2023	08:15	BBX1641	Arrived

[v-jingzhang · Accepted solution]

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.

[v-jingzhang · Accepted solution]

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.

[micjensen · Accepted solution]

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