How to extract months with data and find n-th value as starting point and n-th value as ending point in Excel Power Query, maybe VBA

Question

I have a data set which consists of Date/Time, Pressure and Custom Column. This represents pressure over time data, where I wanna know my starting point (after 5 minutes) and ending point of -before last value (row) within one month. To help you a bit out, usually the measurements are taking roughly 30-40 mins what you can see on this example down. So it means the amount of data can vary.

The Time column is calculated using:

=([@[Date/Time]]-I5)*1440+L5

This data set represents whole data and all the months with values, and I need separated (filtered) months with these starting/ending points as on the screenshot. I used Power Query a lot to play with data, but maybe there is another method to obtain those values...and make them dynamic when possible for future data.

I will also upload my dummy workbook with whole data set (all the months), filter table with months if needed for your infos and test.

https://docs.google.com/spreadsheets/d/1LGl-eri6ewCni2NJ2wGeoYIf-40KO2Lr/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

Answer 1

In Power Query:

Based on your shared workbook and what you have written, it seems that for any given month, you

edit: minor change in algorithm

• start the minute count after excluding the first entry in the month.
  • If that is a typo/error, just remove the function that removes that first line
• with that second entry = minute 0, return the first entry in or after minute 5 as well as the next to last entry in the table.
• Note that I started with just the Date and Pressure columns

Algorithm

• Add a column of monthYear
• GroupBy monthYear
  • Custom aggregation to
    • Remove the first and last rows of the table
    • Create a list of durations in minutes of each time compared with the first time in month. This will be a minute + fraction of a minute
    • Add that list as a column to the original table
    • Determine the first entry in or after the fifth minute
    • Determine the last entry
    • Filter the month subtable to return those two entries.

If you want to see the result for just a given month, you can filter the result in the resultant Excel table.

M Code
please read the comments and examine the Applied Steps to better understand the algorithm

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"P7 [mbar]", Int64.Type}}),

//add month/year column for grouping
    #"Added Custom" = Table.AddColumn(#"Changed Type", "month Year", 
        each Number.ToText(Date.Month([#"Date/Time"]),"00") & Number.ToText(Date.Year([#"Date/Time"]),"0000")),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"month Year"}, {
        //elapsed minutes column
        {"Elapsed Minutes", (x)=> let

        //remove first and last rows from table  
            t=Table.RemoveColumns(Table.RemoveFirstN(Table.RemoveLastN(x)),"month Year"),

        //add a column with the elapsed minutes
            TableToFilter = Table.FromColumns(
                Table.ToColumns(t) 
                & {List.Generate(
                    ()=>[em=null, idx=0],
                    each [idx]< Table.RowCount(t),
                    each [em=Duration.TotalMinutes(t[#"Date/Time"]{[idx]+1} - t[#"Date/Time"]{0}), idx=[idx]+1],
                    each [em])}, type table[#"Date/Time"=datetime, #"P7 [mbar]"=number, elapsed=number]),

        //filter for last entry (which would be next to last in the month
            maxMinute = List.Max(TableToFilter[elapsed]),

        //filter for first entry in the 5th minute
            fifthMinute = List.Select(TableToFilter[elapsed], each Number.IntegerDivide(_,1)>=5){0},

        //select the 5th minute and the last row
            FilteredTable = Table.SelectRows(TableToFilter, each [elapsed]=fifthMinute or [elapsed]=maxMinute)
            in FilteredTable,type table[#"Date/Time"=datetime, #"P7 [mbar]"=number, elapsed=number]}
            }),

    //remove uneeded column and expand the others
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"month Year"}),
    #"Expanded Elapsed Minutes" = Table.ExpandTableColumn(#"Removed Columns", "Elapsed Minutes", {"Date/Time", "P7 [mbar]"}, {"Date/Time", "P7 [mbar]"})
in
    #"Expanded Elapsed Minutes"

Results from your shared workbook data

In Office/Excel 365

Filter Column (eg for January 2020)

E4: 1/1/2020
E5: 1/1/2020

Results

F4 (date/time 5th minute): =IF(COUNTIFS(Table1[Date/Time],">="&E4,Table1[Date/Time],"<" & EDATE(E4,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
          y, (x-INDEX(x,2))*1440,
          z, XMATCH(5,y,1),
          INDEX(x,z,1)))

G4: (Pressure 5th minute): =IF(F4="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E4)*(Table1[Date/Time]<EDATE(E4,1))),
          y, (INDEX(x,0,1)-INDEX(x,2,1))*1440,
          z, XMATCH(5,y,1),
          INDEX(x,z,2)))

F5: (Date next to last): =IF(COUNTIFS(Table1[Date/Time],">="&E5,Table1[Date/Time],"<" & EDATE(E5,1))=0,"",
LET(x,FILTER(Table1[Date/Time],(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
          INDEX(x,COUNT(x)-1)))

G5: (Pressure next to last):=IF(F5="","",
LET(x,FILTER(Table1,(Table1[Date/Time]>=E5)*(Table1[Date/Time]<EDATE(E5,1))),
          INDEX(x,COUNT(INDEX(x,0,1))-1,2)))