Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LouiseSemaj
Frequent Visitor

Identify missing sample intervals

 

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

 

Before After Missing Sample Intervals.JPG

1 ACCEPTED SOLUTION

HI @LouiseSemaj

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
Phil_Seamark
Employee
Employee

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)        

image.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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.

 

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

HI @LouiseSemaj

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.