How to Perform Identify Gaps/Sequence Check in PowerPivot Without Using DAX a Simpler Way

Welcome to My Wiki Articles on PowerPivot Section.

I would want to explain the Data  Analysis and Fraud Investigations functions in my Series of Wiki Articles on PowerPivot So dont forget to check my other Articles on Wiki to check all the Data Analysis and Fraud Analysis Functions.

This is the First Article of such Series.

I would want to explain here how can one perform the missing item in a sequence/ Identify Gaps in a column in PowerPivot Data such functions are mainly used for Data Analysis.
Lets assume we have a SalesTransactions File which has fields such as 

 

In This File, there are Total of 6664 Records, and The Starting Invoice Number is 1001 and Ending Invoice Number is 7664, So It gives a picture that all the Invoice Numbers are present in this File.

But there are chances that there are Blanks, Repeated Invoice Numbers and Missing Invoice Numbers. So now let us perform a function by which we can come to know the missing Invoice numbers by a simple Sequential Check or Identify Gaps without using DAX.

Step 1 : Create a Dummy Table for Actual Invoices with two fields SlNo and InvoiceNoActuals like shown below put the InvoiceNo from 1001 to 7664 correctly, so this becomes the Master.

Step 2:  Create a Relationship Between InvoiceNo the the TransactionFile and InvoiceNumbers Field in the Dummy Table.

Step 3. Create Pivot Table with as shown in the Image.

We get the results like this

Please let me know if you need any clarifications on this.

Cheers

ManjunathRV