how to find missing numbers in an otherwise sequential list

[gary_beswick]

if these are values in a field called ID:   1,2,3,4,5,6,8,9,10,11,12,13,14,16.......

How do i develop a way to automatically identify the fact that 7 and 15 are missing

[atlas_nordman]

Thank you for the elegant solution. However, I went crazy before realizing I had to sort the merged table "Ascending" after the "Full sequence" column.  

[Anonymous · Accepted solution]

Depending on the goals it can be done in DAX and Power Query.

Here's the PQ version:

• Load the data into PQ
• Find the min and the max of that data
• use that min and max to generate all the values between those two 
• This is your "Full Table"
• 
• Duplicate the original query
  • Merge with the full query
  • From Original to Full Table
  • Right Outer
• Expand the the table
• You will see Null in the original table column, and what that value is in the new column
• 
• can filter on the null to see what values are missing
• 

In DAX I believe you would use GENERATESERIES and pretty much do the exact thing. 

PBIX File is here:

https://1drv.ms/f/s!AoQIGRpzoxRHgbxT1CxyVGNGYUXqgQ