Power Bi Subset table averaged over interval

i'm looking for some help as a novice user.

I have a table which has >40k rows of data at a high resolution, I'd like to subset that database by creating an average over an interval. Unfortunately I'm not sure how's best to go about it. Can it be done with a SELECTCOLUMNS and CALCULATE functions or would I need a LOOP or something totally different.

An example of the before and after below: original table and subset table

Thank you so much for your help in advance!

see above image for expected outcome.

Solution

Your expected output is missing a row for 402 to 403, assuming from is inclusive, and to is exclusive.

DAX
You can create a Calculated Table with a similar DAX expression:

YourNewTable = 
  GROUPBY(
    ADDCOLUMNS(
      YourTable,
      "Depth_from_[m]", ROUNDDOWN([Depth_[m]]], 0)
    ),
    Depth_from_[m],
    "Depth_to_[m]", MINX(CURRENTGROUP(), ROUNDDOWN([Depth_[m]]], 0) + 1),
    "Av_Magnetic_susc", AVERAGEX( CURRENTGROUP(), [Magnetic_susc] ),
    "Av_Gamma_TC", AVERAGEX( CURRENTGROUP(), [Gamma_TC] )
  )

PowerQuery
You can do the same in PowerQuery where you would add a Custom Column with Depth_[m] rounded down. Then do a Group By on this new column with the two average aggregates. And finally, add another Custom Column for the to.

For example:

let
    Source = YourTable,
    #"Added Custom" = Table.AddColumn(Source, "Depth_from_[m]", each Number.RoundDown([#"Depth_[m]"])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Depth_from_[m]"}, {{"Av_Magnetic_susc", each List.Average([Magnetic_susc]), type nullable number}, {"Av_Gamma_TC", each List.Average([Gamma_TC]), type nullable number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Depth_to_[m]", each [#"Depth_from_[m]"] + 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Depth_from_[m]", "Depth_to_[m]", "Av_Magnetic_susc", "Av_Gamma_TC"})
in
    #"Reordered Columns"