cancel
Showing results for
Did you mean:
Helper I

## Detect Non-consecutive values in a column

I Have a simple table with one column

S. No.

1

2

3

4

5

7

8

9

I want to be able to detect missing values in this column (e.g. in this case 6).

Is this possible? Thanks! @amitchandak

2 ACCEPTED SOLUTIONS
Super User IV

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 ( 191 )
VAR missingnumbers =
EXCEPT ( fulllistofnumbersnumbersincolumn )
RETURN
CONCATENATEX ( missingnumbers, [Value], "," )

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Community Support

Hi @newbieuser ,

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.

5 REPLIES 5
Super User IV

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 ( 191 )
VAR missingnumbers =
EXCEPT ( fulllistofnumbersnumbersincolumn )
RETURN
CONCATENATEX ( missingnumbers, [Value], "," )

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper I

That works. Thanks! Is there a way to re-route the output to individual values in a new table's column?

Community Support

Hi @newbieuser ,

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.

Helper I

Great Thanks!

Community Support

Hi @newbieuser ,

Could you please Accept my post as the solution as well if it could help you a little?

Best Regards,
Eyelyn Qin

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.