average of repeating regions

[psvdr]

Dear Community,

I'm asking for your kind help for an M-code soulution for the following:

I have stream of measurement records in a block of 200 rows with a total of more than 10k rows, may be visualised as:

1	first record, meas. 1
...
200	last record, meas. 1
201	first record, meas. 2
...
400	last record, meas. 2
401	first record, meas. 3
...
600	last record, meas. 3
...

I'd like to calculate the average of that 30 records those are 10 records before the end of the measurment (row 161 to 190 for every 200). expected outcome:

1
...
190	avrg of 161:190
...
390	avrg of 361:390
...
590	avrg of 561:590
...

Couldn't find a proper way of grouping.

Thank you in advance.

[v-achippa]

Hi @psvdr ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @tharunkumarRTK and @Akash_Varuna for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[tharunkumarRTK]

@psvdr 

I am providing some M code to achive the result, please understand the code and make changes as per your current data model schema. And for further help, please share the sample data

let

    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],


    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),


    AddGroupColumn = Table.AddColumn(AddIndex, "GroupID", each Number.IntegerDivide([Index] - 1, 200) + 1, Int64.Type),


    GroupedData = Table.Group(AddGroupColumn, "GroupID", {{"AllData", each _, type table [Index=Int64.Type, Column1=any]}}),


    ProcessedGroups = Table.AddColumn(GroupedData, "FilteredData", each 
        let 
            GroupTable = [AllData],  
            SortedTable = Table.Sort(GroupTable, {{"Index", Order.Descending}}),  // Sort by Index Descending
            RemoveBottom10 = Table.RemoveLastN(SortedTable, 10),  // Remove bottom 10 rows
            KeepBottom30 = Table.LastN(RemoveBottom10, 30),  // Keep the last 30 rows
            AverageValue = List.Average(KeepBottom30[Column1])  // Compute the average of the remaining rows
        in 
            AverageValue
    )



in
    ProcessedGroups

After this step you can expand the table column to find the average values

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[psvdr]

Thank you for your fast response.

Sorry for the late reply: some time was needed to digest row by row & to fine tune your code: in the ProcessedGroups section, the order must be in Ascending order to sort the rows from 161 to 190.

Can you please change it in your post, not to confuse others ? 🙂

Would it be possible to perform the ProcessedGroups calculation for 4 columns?

[v-achippa · Accepted solution]

Hi @psvdr,

Thank you for the response and Iam glad to hear that the issue is resolved now after fine tuning the code and especially adjusting the ProcessedGroups section to sort the rows from 161 to 190 in ascending order. I request you to please mark this post or your own post as Accept as Solution so that other community members will find it more easily.

Thanks and regards,

Anjan Kumar Chippa

[Akash_Varuna]

Hi @psvdr 

• Add an Index Column: This helps identify each row uniquely.

• Calculate a Grouping Identifier: Use integer division (Index  / 200) to identify each 200-row block.

• Filter Rows for Averaging: Extract rows from 161 to 190 in each 200-row block.

• Compute the Average: Group the filtered rows and calculate the mean.

• Merge Back with Original Data: Ensure the result aligns with the correct rows in the dataset.