Power Query to Sum If Time Within Interval

I have a massive XLSX export file that contains thousands of trading transactions. Sometimes there are many rows that below to the SAME order.

I want to run a power query (or other) that will consolidate multiple rows into ONE row if the trade_done_at column is within 1 second. Since these have the same order_id these multiple rows would become ONE row. All other rows would remain untouched.

So, in the attached file, the first 3 rows would be grouped together due to the same order_id.

test-data.xlsx

Column deal_price would be an avg of the values.
Columns for amount, funds, fee would be sum of the values.
All others would just have the same value.

Using EXCEL for MAC - V 16.66.1

How can I do this in excel?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.