We have an accounting software that produces terrible GL reports.  I have the ability to export all transaction lines (using Dataverse and Power Query) and filter for the columns that are needed for the report.

I tried creating a PivotTable report by Acct Type and GL Number, but obviously, I cannot give it a beginning balance line therefore, the ending balances are not correct.

I thought about making a report using the SUMPRODUCT to create a beginning balance line for each GL account and the FILTER function to return all the rows for the accounting period under the Beg Bal line. But the rows will be dynamic each period and the filter function will break.

What other formulas or options can I try to create a monthly General Ledger report.

This is a multi-entity company that will need a general ledger for each entity.  I would like to be able to use a drop down list to select my entity and accounting period.  My report will have 14 columns across and unlimited rows down.