Cleaning SAP data dumps with Power Query: a repeatable month-end routine

SAP line-item reports such as FBL3N (G/L account line items), FBL1N (vendor) and FBL5N (customer) export well enough to Excel, but the output is rarely analysis-ready: report title rows, subtotal lines, numbers stored as text, dates in a format your Excel does not recognise, and trailing minus signs. Cleaning that by hand every month is slow and error-prone. Power Query, built into Excel 2016 and later, records the cleaning steps once and replays them on every new export.

1. Export consistently

Save a report layout in SAP with the columns you need (company code, G/L, posting date, document number, document type, amount in local currency, text, cost centre) so every export has the same shape.
Export as a spreadsheet (XLSX) rather than "unconverted" text where possible.
Save each month's file into one folder, for example MIS\SAP exports\FBL3N\, with the period in the file name.

2. Load the whole folder

In Excel: Data → Get Data → From File → From Folder, choose the folder, then Combine & Transform.
Power Query creates a sample-file query; the steps you apply to it are applied to every file in the folder, and a Source.Name column tells you which file each row came from.

3. Clean the rows

Remove Top Rows to drop report title lines, then Use First Row as Headers.
Filter out blank rows and subtotal lines (for example rows where the document number is null or the text starts with "*").
Trim and Clean text columns to remove invisible spaces that break lookups.

4. Fix numbers and dates with locale

SAP user settings decide the formats. A user with the European format exports 1.234,56 and 31.03.2026; a trailing minus such as 1,234.56- marks a credit. Do not change types with the default button:

For trailing minus signs, add a custom column: if Text.EndsWith([Amount], "-") then -Number.From(Text.TrimEnd([Amount], "-")) else Number.From([Amount]), or first use Replace Values to move the sign.
Right-click the column → Change Type → Using Locale… and choose the data type and the locale that matches the export (for example German (Germany) for 1.234,56 and dd.mm.yyyy; English (India) for 1,23,456.00 and dd-mm-yyyy).

5. Unpivot period columns

Balance reports such as FAGLB03 or S_ALR-style trial balances often put one column per period. Analysis is far easier with one row per account per period. Select the descriptive columns (account, description), then Transform → Unpivot Other Columns. Rename the new columns to Period and Amount and change Period to a date.

6. Map to the MIS structure

Keep a small mapping table in the workbook: G/L account → MIS line → Department. Load it as a query and use Merge Queries (left outer join) on G/L. Add a check: filter the merged result for rows where the MIS line is null. Those are new accounts that need mapping before the pack is final.

7. Refresh each month

Drop the new month's export into the folder.
Data → Refresh All.
Check the control totals: the sum by period should tie to the trial balance.

Because the heavy lifting happens in Power Query, the reporting sheets can use plain SUMIFS or a PivotTable on one clean table, with no volatile formulas. See why volatile formulas slow down an MIS pack.

8. Controls worth adding

Row count and amount total per source file, compared with the SAP report footer.
A list of unmapped accounts and of documents with blank cost centres.
Duplicates by document number and line item. The duplicate and variance analyzer can run these checks on the cleaned table.