Power Query Problems

How to Create a Dynamic GL Code and Entity Report in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 10, 2026 869 views

Question details

The user needs to transform a large financial dataset containing GL codes in columns and entity codes in rows into a clean, dynamically refreshable report.

How to Create a Dynamic GL Code and Entity Report in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Summarizing cross-tabular financial data into a monthly refreshable summary report.
Observed behavior
The raw data requires reshaping from a wide layout to a normalized tabular format to allow for accurate PivotTable summarization.
Before you start

Ensure your raw financial data is formatted as an official Excel Table (select the data and press Ctrl+T). This ensures that any new rows or columns added in the future will automatically be captured in your report updates.

Solution 1Recommended

Use Power Query to Unpivot Data and Generate a PivotTable

Transform your cross-tabular GL data into a normalized layout using Power Query, then summarize it dynamically with a PivotTable.

Power Query is the ideal tool for reshaping large financial datasets where General Ledger (GL) codes are spread across multiple columns. By unpivoting these columns, you convert the data into a flat, tabular format that easily feeds into a refreshable PivotTable.

1
Load data into Power Query

Select any cell within your financial data table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

2
Unpivot the GL code columns

In the Power Query Editor, hold down the 'Ctrl' key to select all columns representing your entity codes. Right-click any of the selected column headers and choose 'Unpivot Other Columns'. This action shifts your GL codes into a single, combined column.

3
Filter out zero values

To keep your resulting report clean, click the filter dropdown arrow on your newly created 'Value' column header, uncheck the '0' option, and click 'OK'.

4
Load the normalized data

Click 'Close & Load' from the Home tab, then select 'Close & Load To...'. Choose 'PivotTable Report' to directly create a dynamic summary based on your restructured dataset.

5
Build and refresh your report

In the PivotTable Fields pane, drag Entity codes to the Rows area, GL codes to the Columns area, and your amounts to the Values area. Whenever you add new monthly data to the source table, simply right-click the PivotTable and select 'Refresh'.

Use Power Query to Unpivot Data and Generate a PivotTable
Dynamic Updates: Because the source is structured as an Excel Table, newly added GL code columns or entity rows will automatically appear in your output the next time you refresh the query.
Free Microsoft Office alternative

Easily Manage Financial Reports with WPS Office

While advanced Power Query unpivoting is a feature specific to Microsoft Excel, WPS Spreadsheet offers a robust, free alternative for standard financial data management. You can seamlessly open your Excel files, utilize complex formulas, and create dynamic PivotTables to summarize your data without paying costly subscription fees.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats and existing PivotTables.Lightweight software designed to handle large financial datasets smoothly.Familiar user interface makes it easy to transition and build reports instantly.Completely free to download and use for your everyday accounting and data tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Can I add new GL codes to the source data without rebuilding the entire report?

Yes, as long as your original source data is formatted as an Excel Table. When you add new columns for new GL codes, Power Query automatically includes them the next time you right-click your resulting PivotTable and select 'Refresh'.

Why do my entity codes show up multiple times after unpivoting?

Unpivoting transforms the cross-tabular layout into a flat dataset where every single intersection of an entity and a GL code gets its own individual row. Summarizing this flattened data later with a PivotTable will group them back together logically.

How do I remove empty or zero-value rows to keep the GL report clean?

While inside the Power Query Editor, click the dropdown arrow on the header of your generated 'Value' column and uncheck the box next to '0'. This filters out all unnecessary zero values before loading the clean data back into your Excel sheet.