How to take a set of data in excel and make a list that includes every data point?

Question

I have a large data set that I need to make into a list. The data involves arrival of packages. I would need the list to be arranged by arrival time.

Currently my data looks like this.

During hour 1 (1:00) 2 blue, 3 red, 1 yellow, and 2 orange packages arrive.

During hour 2 (2:00) 5 blue, 5 red, 0 yellow, and 3 orange packages arrive.

From this data I would need to create a list arranged by arrival time for all the packages. It should look something like this.

Is there an excel function(s) that can help with this procedure?

Answer 1

You can use PowerQuery to solve the question.

Format your Data as table (CTRL-T), name the table tbl_Packages and use this M-Code:

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_Packages"]}[Content],
    Change_type = Table.TransformColumnTypes(Source,{{"Arrival Time", type text}, {"01:00", Int64.Type}, {"02:00", Int64.Type}, {"03:00", Int64.Type}, {"04:00", Int64.Type}}),
    Unpivot_Other = Table.UnpivotOtherColumns(Change_type, {"Arrival Time"}, "Time", "Count"),
    Filter_Rows = Table.SelectRows(Unpivot_Other, each ([Count] <> 0)),
    Custom_Column = Table.AddColumn(Filter_Rows, "LIst", each List.Numbers(1,[Count])),
    Expand_List = Table.ExpandListColumn(Custom_Column, "LIst"),
    Remove_other_columns = Table.SelectColumns(Expand_List,{"Arrival Time", "Time"}),
    Sort_Rows = Table.Sort(Remove_other_columns,{{"Time", Order.Ascending}, {"Arrival Time", Order.Ascending}})
in
    Sort_Rows