locked
Group 911 calls by Incident number then Sort by Dispatch DateTime Stamp then Rank (1,2,3) but bypass null values RRS feed

  • Question

  • We are an accredited fire department and need to analyze our incident response times based upon numerous factors.  We need to rank each of the following performance measures (PM) without adversely affecting the next PM.

    1. Alarm Handling Time (Call Received to Dispatch of Fire Resources)
    2. Turnout Time (Dispatch to Enroute of Fire Resources)
    3. Travel Time (Enroute to Onscene of Fire Resources)
    4. Total Response Time 'TRT'  (Call Received to Onscene of Fire Resources)

    Each of these PMs has an individual threshold such as Alarm Handling:

    • Result shall be: >00:00:00 and <=00:05:00
    • 1st Unit Dispatched (Rank based upon Group Incident Number then minimum Dispatch DateTime Value then Rank)
    • Fire Dept Unit not Mutual Agency

    We need to rank these by earliest DateTime stamp, but bypassing the null values in the ranking.  And based upon the PM we might be analyzing not only the 1st Due Unit but also the 2nd.

    INC                     UNIT              DISPATCH                  RANK

    VFC1500020        PAS_Unit        1/1/15 00:47:00         1

    VFC1500020        PAS_Unit        1/1/15 00:47:27         2

    VFC1500020        null                1/1/15 00:47:27         null

    VFC1500025        PAS_Unit        null                            null

    VFC1500025        PAS_Unit        1/1/15 01:00:31         1

    VFC1500025        PAS_Unit        1/1/15 01:00:31         2

    VFC1500025        PAS_Unit        1/1/15 01:00:58         3

    Wednesday, April 24, 2019 2:48 AM

Answers

  • The following is one way to accomplish this task. You need to substitute the source in the code with your actual source:

    let
        source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        addedIndex = Table.AddIndexColumn(source, "Index", 1, 1),
        filteredRows = Table.SelectRows(addedIndex, each ([UNIT] <> null) and ([DISPATCH] <> null)),
        groupedRows = Table.Group(
            filteredRows, 
            {"INC"}, 
            {{"Table", each Table.AddIndexColumn(_, "RANK", 1), type table [INC=text, UNIT=text, DISPATCH=datetime, RANK=number]}}
        ),
        combinedTables = Table.Combine(groupedRows[Table]),
        mergedTables = Table.NestedJoin(addedIndex, "Index", combinedTables, "Index", "Combined"),
        expandedColumn = Table.ExpandTableColumn(mergedTables, "Combined", {"RANK"}),
        sortedRows = Table.Sort(expandedColumn,{{"Index", Order.Ascending}}),
        removedIndex = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedIndex


    Wednesday, April 24, 2019 10:35 PM

All replies

  • It appears that you've provided how you want your output to look, but what does the input table look like? 
    Wednesday, April 24, 2019 8:37 PM
  • Sorry.  The first 3 columns represent the input table.  The fourth column represents the output.
    Wednesday, April 24, 2019 9:22 PM
  • The following is one way to accomplish this task. You need to substitute the source in the code with your actual source:

    let
        source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
        addedIndex = Table.AddIndexColumn(source, "Index", 1, 1),
        filteredRows = Table.SelectRows(addedIndex, each ([UNIT] <> null) and ([DISPATCH] <> null)),
        groupedRows = Table.Group(
            filteredRows, 
            {"INC"}, 
            {{"Table", each Table.AddIndexColumn(_, "RANK", 1), type table [INC=text, UNIT=text, DISPATCH=datetime, RANK=number]}}
        ),
        combinedTables = Table.Combine(groupedRows[Table]),
        mergedTables = Table.NestedJoin(addedIndex, "Index", combinedTables, "Index", "Combined"),
        expandedColumn = Table.ExpandTableColumn(mergedTables, "Combined", {"RANK"}),
        sortedRows = Table.Sort(expandedColumn,{{"Index", Order.Ascending}}),
        removedIndex = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedIndex


    Wednesday, April 24, 2019 10:35 PM