Search code examples
powerbioverlapduration

In Power Bi, how do I detect overlapping events and then only show certain events?

I'm building a scheduler in Power Bi that takes data from our scheduling tool. It simply adds up the count of each event for each half hour/15 minute interval. The issue is that the data source allows for events to overlap, i.e. you can be scheduled on phones from 1230 to 1300, but you can also schedule a lunch for that same time. The system has priority levels for each event (i.e. Lunch is 999 while phones is 1, so Lunch should have priority). I want to be able to only show the highest priority event for each half hour, for each person and having some trouble with the following:

  1. Seeing what's overlapping
  2. Figuring out how to just see the priority event (there can be up to 5 events overlapping based on how some managers have made their schedules.
Agent ID Start Time End Time ExceptionType Priority Half Hour
1 4/17/2023 1230 4/17/2023 1300 Phones 1 1230
1 4/17/2023 1230 4/17/2023 1300 Lunch 999 1230

I used this video to figure out the overlaps, but it's not helping me figure out how to create a unified column that would basically be "Max priority event." https://www.youtube.com/watch?v=SfcHsB6uWjE

Any help would be appreciated!

Solution

  • You can filter out all but the highest priority by grouping by the other columns and ranking and filtering the nested table.

    eg

    let
    Source = Web.BrowserContents("https://stackoverflow.com/questions/76093331/in-power-bi-how-do-i-detect-overlapping-events-and-then-only-show-certain-event"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.s-table > * > TR > :nth-child(1)"}, {"Column2", "TABLE.s-table > * > TR > :nth-child(2)"}, {"Column3", "TABLE.s-table > * > TR > :nth-child(3)"}, {"Column4", "TABLE.s-table > * > TR > :nth-child(4)"}, {"Column5", "TABLE.s-table > * > TR > :nth-child(5)"}, {"Column6", "TABLE.s-table > * > TR > :nth-child(6)"}}, [RowSelector="TABLE.s-table > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Agent ID", Int64.Type}, {"Start Time", type text}, {"End Time", type text}, {"ExceptionType", type text}, {"Priority", Int64.Type}, {"Half Hour", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Agent ID", "Start Time", "End Time", "Half Hour"}, {{"Rows", each _, type table [Agent ID=nullable number, Start Time=nullable text, End Time=nullable text, ExceptionType=nullable text, Priority=nullable number, Half Hour=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "AddRank", each Table.AddRankColumn([Rows], "Rank",{"Priority", Order.Ascending}, [RankKind = RankKind.Ordinal] )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "TopRank", each Table.SelectRows([AddRank], each [Rank]=1)),
    #"Expanded TopRank" = Table.ExpandTableColumn(#"Added Custom1", "TopRank", {"ExceptionType"}, {"TopRank.ExceptionType"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded TopRank",{"Rows", "AddRank"})
in
    #"Removed Columns"