Identify missing sample intervals

[LouiseSemaj]

I have a table of data and I want to identify the intervals that are missing samples, and then go on to create a record to indicate that.  See the before and after tables below (I could not work out how to post an xls or csv attachment?).  By looking at the From/To depths you can identify where the missing samples are.  So where the From of one interval does not match the To of the next interval, there is an interval that is not sampled.  I would then like to add in a row to indicate the interval that is not sampled (so Reading =0).  I am hoping that someone in this forum may have come across such a problem before.  I am a newbee at Power Query/BI, and after any help I can get.  Many thanks and kind regards

[Phil_Seamark]

Hi @LouiseSemaj

Please try this DAX calculated table.  I have attached a PBIX file for you.

Table = 
VAR T1 = 
    ADDCOLUMNS(
        'Table1',
        "Next From",MINX(
                        FILTER(
                             'Table1',
                             'Table1'[Location] = EARLIER('Table1'[Location]) && 
                             'Table1'[From] > EARLIER('Table1'[From])
                             ),
                        'Table1'[From])
                        )
                        
VAR T2 = 
        SELECTCOLUMNS(
             FILTER(T1,[Next From]<>'Table1'[To]),
             "Location",[Location] ,
             "From",[To] ,
             "To",[Next From] ,
             "Reading" , 0
        )
RETURN UNION(Table1,T2)        

[LouiseSemaj]

Excellent.  Many thanks Phil.  This looks like it is right on the mark.  However, I am not sure exaclty how to progress here.  I see the pbix file.  I opened and see it has the data, with data type changes to dec.  How then do I use the other code?  Copy and paste into BI advanced editor?  Really appreciate any assistance you can give me here.  Kind regards

[Phil_Seamark]

Hi @LouiseSemaj

The code I sent you was to create a DAX calculated table

Just click the New Table button on the ribbon and past the code in.  You may have to adjust my code to fit the names of your tables.

[LouiseSemaj]

Great.  I have got it working in BI.  Is there a way this could be achieved in Power Query, without the DAX code side of things I guess??  Thanks Phil

[Phil_Seamark · Accepted solution]

HI @LouiseSemaj

I have attached an updated PBIX file that makes the same transforms in Power Query

[LouiseSemaj]

Awesome.  Many thanks Phil  I have it sorted and been able to follow your very clear steps and rebuild it myself in power query.  Many thanks again for your very prompt assistance  (Apologies I was also needing a 0-216 row indicating no sample also?) Any hints there.