Counting consecutive blanks in a sequence of numbers only once

[noneother]

Hello

I’m trying to count the number of blanks in a sequence of numbers, however, where the sequence encounters multiple blanks I only want to count this multiple blanks once.

E.g.

Col A

=====

1

2

Blank()

Blank()

5

6

Blank()

Blank()

9

10

In the above table, I only want to return the number of blanks counted = 2.

[Jos_Woolley]

Hi,

Again, assuming columns named Column1 and Index:

MyMeasure :=
CALCULATE(
    COUNTROWS(
        FILTER(
            Table1,
            ISBLANK( Table1[Column1] )
                && NOT (
                    ISBLANK( LOOKUPVALUE( Table1[Column1], Table1[Column1], Table1[Index] - 1 ) )
                )
        )
    )
)

Regards

[tamerj1]

Hi @Jos_Woolley 

Clean and simple 👍 I like it 👌

will it count the blank if it was in the first row?

Also can please help me understand the need for CALCULATE?

[Jos_Woolley]

@tamerj1 

Guilty on both counts! 😊 No need for CALCULATE and it doesn't work if the first entry is blank.

Back to the drawing board...

[tamerj1]

Yes I've been there too 😅

please check up my solution and let me know your openion

[tamerj1]

@Jos_Woolley 

Your code should work perfectly with little modification

MyMeasure =
VAR UniqueBlanksTable =
FILTER (
Table1,
ISBLANK ( Table1[Column1] )
&& NOT ISBLANK ( LOOKUPVALUE ( Table1[Column1], Table1[Column1], Table1[Index] - 1 ) )
)
VAR UniqueBlanksCount =
COUNTROWS ( UniqueBlanksTable )
VAR FirstPotentialBlankRowInFilter =
FILTER (
Table1,
ISBLANK ( Table1[Column1] )
&& Table1[Index] = MIN ( Table1[Index] )
)
VAR Result =
IF (
COUNTROWS ( FirstPotentialBlankRowInFilter ) = 0,
UniqueBlanksCount,
UniqueBlanksCount + 1
)
RETURN
Result

[Jos_Woolley]

Thanks, tamerj1.

Having looked at it again, I'm wondering why I can't just amend it to simply:

MyMeasure :=
COUNTROWS(
    FILTER(
        Table1,
        ISBLANK( Table1[Column1] )
            && (
                Table1[Index] = 1
                    || NOT (
                        ISBLANK( LOOKUPVALUE( Table1[Column1], Table1[Column1], Table1[Index] - 1 ) )
                    )
            )
    )
)

Guess I'm missing something obvious?

Thanks for all your input - much appreciated.

Regards 

[tamerj1]

Actually that should work. Good job 👍 

I will try it later with different senarios but as I said I believe it should work. 

[tamerj1]

@Jos_Woolley  But use 

 Table1[Index] = MIN (Table1[Index] ) instead of Table1[Index] = 1 to keep it dynamic with the filter context. But I think you need to store it in a variable outside filter. 

[tamerj1]

Hi @noneother 
Here is my solution using a measure https://www.dropbox.com/t/MY1X01Z4Z1sqyD8h

Index row is a must have. This code should work wherever the location of the gap is (at the top, bottom, midle)of the table) and no matter the number of blank rows is in each gap. You can even filter the table on whatever conditions you want and the number of gaps will change accordingly. 

Number of Gaps = 
VAR BlankTable =
    FILTER (
        'Table',
        ISBLANK ( 'Table'[Col A] )
    )
VAR NewIndexTable =
    ADDCOLUMNS (
        BlankTable,
        "@NewIndex", RANKX ( BlankTable, 'Table'[Index], 'Table'[Index], ASC )
    )
VAR IndexDifferenceTable =
    ADDCOLUMNS (
        NewIndexTable,
        "@IndexDifference", 'Table'[Index] - [@NewIndex]
    )
VAR DifferenceColumn = 
    SELECTCOLUMNS (
        IndexDifferenceTable,
        "@Difference", [@IndexDifference]
    )
VAR Result = COUNTROWS ( DISTINCT ( DifferenceColumn ) )
RETURN
    Result

• First the table is filtered to keep only blank rows.
• Then a new index column is added based on the ranking of the values of whever left out of the original index after filtration. 
• Then another column that computes the difference between the original and the new index columns is added.
• The diffrence column is selected. 
• The distinct count of the diffence column will gove the number of gaps. 

[v-stephen-msft]

Hi @noneother ,

Here's my solution.

1.Create an index column.(You can add it in the power query or using dax).

2.Create the first measure to move the value of column1 one row down.

Measure = CALCULATE(MAX('Table'[Column1]),FILTER(ALLSELECTED('Table'),[Index]=MAX('Table'[Index])-1))

Remember to turn on "Show items with no data", otherwise the data will be incomplete.

3.Create the second measure to get the blank value(de-duplicated).

Measure 2 = IF(MAX('Table'[Column1])=[Measure],1)

4.Create the last measure to sum the [Measure 2].

COUNT = SUMX(ALL('Table'),[Measure 2])

Best Regards,

Stephen Tao

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

[CNENFRNL · Accepted solution]

The power of Excel worksheet formula is beyond your imaginaiton,

Iteration is way much easier in PQ than in Excel,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVZyBJMIwghMGqOJmqLxnZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Val = _t]),
    Gaps = let l={"dummy"}&Source[Val] in List.Accumulate({0..List.Count(l)-2}, 0, (s,c) => if Text.Length(l{c})>0 and Text.Length(l{c+1})=0 then s+1 else s)
in
    Gaps

[OwenAuger]

Hello @noneother 

Just clarifying the problem: Do you want to count the number of "blocks" of consecutive blank values, irrespective of the values of the numbers surrounding them?

If so, we would need an additional index column to define the ordering of the rows. Can we assume that an index column exists?

e.g.

Col A	Index
1	1
2	2
blank	3
blank	4
5	5
6	6
blank	7
blank	8
9	9
10	10

Alternatively, is this like the classic islands/gaps problem where a "gap" corresponds to missing values, and in this example there are two gaps because 3-4 and 7-8 are missing?

Regards,

Owen