Detect Non-consecutive values in a column

[Anonymous]

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 

[mahoneypat · Accepted solution]

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

[mahoneypat · Accepted solution]

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

[Anonymous]

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

[v-eqin-msft · Accepted solution]

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.

[Anonymous]

Great Thanks!

[v-eqin-msft]

Hi @Anonymous ,

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

Thanks in advance!😀

Best Regards,
Eyelyn Qin