Detect Non-consecutive values in a column

You can create a virtual table of numbers with GENERATESERIES and then check for missing ones with the EXCEPT function (and then display the table of missing values with CONCATENATEX.  Replace Number[Value] with your actual table/column name.

 

Missing Numbers =
VAR numbersincolumn =
    DISTINCT ( Numbers[Value] )
VAR fulllistofnumbers =
    GENERATESERIES ( 1, 9, 1 )
VAR missingnumbers =
    EXCEPT ( fulllistofnumbers, numbersincolumn )
RETURN
    CONCATENATEX ( missingnumbers, [Value], "," )

 

Pat

 

Hi Anonymous ,

 

Actually I'm a little confused about your expected output.

In case you want to add the Full number as a column and then combine the original missing column as a new table, you could try this:

 

1. Create a new table:

New table = GENERATESERIES ( MIN('Table'[S.No.]), MAX('Table'[S.No.]), 1 )

2.Use LOOKUPVALUE() to get the matched original column:

original column = LOOKUPVALUE('Table'[S.No.],'Table'[S.No.],[Value])

 

The final output is shown below:

 

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