Returning Table Summarization with Breaks in Time series

[Anonymous]

Hi!
 

I am trying to detect if a data set is consistent occurrences or has breaks.

For example, let's say I have a list of types (Q1-Q4) and the times that they occur.

See below:

I want to summarize a table that will look at each type (Q1-Q4) and return a column for day, hour, minute (and eventually second). Each column will return either "consistent" or "break".

For example, Q1 has a duration of 3 minutes and has one occurrence each minute.

Q2, has a duration of ~3 hours, but there is no occurrence when the hour is 3 and it does not occur consistently every minute.

Q3, has a duration of ~5 days, but there is no occurrence when the day is 14, 15, or 16. Nor does it occur consistently every hour or minute.

Q4 has a duration of ~3 hours. It does not occur every minute, but it does occur every hour during its lifespan.

The result would be the table below.

Help very much appreciated!

[kentyler · Accepted solution]

This is not the full answer. But it provides an algorithm for finding the breaks

I added an idex, that is a critical step, as I use the index to "move up one"

Then I created some calculated columns to extract month, day, hour, minute from the date

Then for each type of value i wrote a calculated column that calculated whether it was the same, or incremented by one from the previous date. If not I marked it as a "break"

The comparisons take the "quarter" into account, so when the value in the quarter column changes the first line is never a break, as it has no "previous" line to compare itself with.
If you need help parsing the code let me know.

Day Break =

var index = Sheet1[Index]

var prev_index = index -1

var cur_q = Sheet1[quarter]

var prev_date = Calculate(max(Sheet1[dateandtime]),all(Sheet1),Sheet1[index] = prev_index,Sheet1[quarter] = cur_q)

var cur_day = Sheet1[Day]

var prev_day = Day(prev_date)

var same_num = prev_day = cur_day

var increments_by_one = prev_day + 1 = cur_day

var is_break = if (not isblank(prev_date),if(same_num,0,if(increments_by_one,0,1)),0)

return is_break

Month Break =

var index = Sheet1[Index]

var prev_index = index -1

var cur_q = Sheet1[quarter]

var prev_date = Calculate(max(Sheet1[dateandtime]),all(Sheet1),Sheet1[index] = prev_index,Sheet1[quarter] = cur_q)

var cur_month = Sheet1[Month]

var prev_month = Month(prev_date)

var is_break = if (not isblank(prev_date),if(prev_month = cur_month,0,if(prev_month + 1 = cur_month,0,1)),0)

return is_break

I wrote "month break" first, in the other 2 columns I added more variables to help with debugging and to make the code more readable. 

[Icey · Accepted solution]

Hi @Anonymous ,

Try this:

1. Add Index column in Power Query Editor.

2. Create columns.

Day = DAY('Table'[Time])

Hour = HOUR('Table'[Time])

Minute = MINUTE('Table'[Time])

Day 1 =
VAR PreviousIndex = 'Table'[Index] - 1
VAR PreviousDay_ =
    CALCULATE (
        MAX ( 'Table'[Day] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type] ), PreviousIndex = 'Table'[Index] )
    )
RETURN
    IF ( ISBLANK ( PreviousDay_ ), BLANK (), 'Table'[Day] - PreviousDay_ )

Hour 1 =
VAR PreviousIndex = 'Table'[Index] - 1
VAR PreviousHour_ =
    CALCULATE (
        MAX ( 'Table'[Hour] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type] ), PreviousIndex = 'Table'[Index] )
    )
RETURN
    IF ( ISBLANK ( PreviousHour_ ), BLANK (), 'Table'[Hour] - PreviousHour_ )

Minute 1 =
VAR PreviousIndex = 'Table'[Index] - 1
VAR PreviousMinute_ =
    CALCULATE (
        MAX ( 'Table'[Minute] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type] ), PreviousIndex = 'Table'[Index] )
    )
RETURN
    IF ( ISBLANK ( PreviousMinute_ ), BLANK (), 'Table'[Minute] - PreviousMinute_ )

Day 2 =
VAR PreviousIndex = 'Table'[Index] - 1
VAR PreviousDay_ =
    CALCULATE (
        MAX ( 'Table'[Day 1] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type] ), PreviousIndex = 'Table'[Index] )
    )
RETURN
    IF ( ISBLANK ( PreviousDay_ ), BLANK (), 'Table'[Day 1] - PreviousDay_ )

Hour 2 =
VAR PreviousIndex = 'Table'[Index] - 1
VAR PreviousHour_ =
    CALCULATE (
        MAX ( 'Table'[Hour 1] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type] ), PreviousIndex = 'Table'[Index] )
    )
RETURN
    IF ( ISBLANK ( PreviousHour_ ), BLANK (), 'Table'[Hour 1] - PreviousHour_ )

Minute 2 =
VAR PreviousIndex = 'Table'[Index] - 1
VAR PreviousMinute_ =
    CALCULATE (
        MAX ( 'Table'[Minute 1] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Type] ), PreviousIndex = 'Table'[Index] )
    )
RETURN
    IF (
        ISBLANK ( PreviousMinute_ ),
        BLANK (),
        'Table'[Minute 1] - PreviousMinute_
    )

3. Create measures.

Day Measure = IF ( SUM ( 'Table'[Day 2] ) = 0, "Consistent", "Break" )

Hour Measure = IF(SUM('Table'[Hour 2])=0,"Consistent","Break")

Minute Measure = IF(SUM('Table'[Minute 2])=0,"Consistent","Break")

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentyler]

could you supply a text version of the data table instead of a ping, or a small excel file.

Thanks

[kentyler · Accepted solution]

This is not the full answer. But it provides an algorithm for finding the breaks

I added an idex, that is a critical step, as I use the index to "move up one"

Then I created some calculated columns to extract month, day, hour, minute from the date

Then for each type of value i wrote a calculated column that calculated whether it was the same, or incremented by one from the previous date. If not I marked it as a "break"

The comparisons take the "quarter" into account, so when the value in the quarter column changes the first line is never a break, as it has no "previous" line to compare itself with.
If you need help parsing the code let me know.

Day Break =

var index = Sheet1[Index]

var prev_index = index -1

var cur_q = Sheet1[quarter]

var prev_date = Calculate(max(Sheet1[dateandtime]),all(Sheet1),Sheet1[index] = prev_index,Sheet1[quarter] = cur_q)

var cur_day = Sheet1[Day]

var prev_day = Day(prev_date)

var same_num = prev_day = cur_day

var increments_by_one = prev_day + 1 = cur_day

var is_break = if (not isblank(prev_date),if(same_num,0,if(increments_by_one,0,1)),0)

return is_break

Month Break =

var index = Sheet1[Index]

var prev_index = index -1

var cur_q = Sheet1[quarter]

var prev_date = Calculate(max(Sheet1[dateandtime]),all(Sheet1),Sheet1[index] = prev_index,Sheet1[quarter] = cur_q)

var cur_month = Sheet1[Month]

var prev_month = Month(prev_date)

var is_break = if (not isblank(prev_date),if(prev_month = cur_month,0,if(prev_month + 1 = cur_month,0,1)),0)

return is_break

I wrote "month break" first, in the other 2 columns I added more variables to help with debugging and to make the code more readable.