In simple words

SAP reports such as FBL1N (vendor line items), FBL3N or FAGLL03 (G/L line items) and FBL5N (customer line items) can be saved to a local file. The file is rarely ready to use. It has page headers, separator lines, subtotal rows, amounts like 1,25,000.00- and dates like 31.03.2026.

Power Query is built into Excel (Data → Get Data) and Power BI. It records each cleaning step once, so next month you only press Refresh. Everything runs on your own computer.

This page is the code companion to the blog post Cleaning SAP data dumps with Power Query, which walks through the same routine with menu clicks.

How to use a snippet: in Excel go to Data → Get Data → From Other Sources → Blank Query → Advanced Editor, paste the code, change the file path or column names, and click Done.

1. A parameter for the file or folder path

Create this once (Home → Manage Parameters → New) so that a colleague can point the query at their own folder without editing code.

// Name the query FolderPath
"C:\Finance\SAP exports\FY2026-27" meta [IsParameterQuery = true, Type = "Text", IsParameterQueryRequired = true]

2. Load an SAP "unconverted" list export (pipe-separated text)

In the report, choose List → Export → Local File → Unconverted. The file has lines like | 0000100023 | 31.03.2026 | 1,25,000.00- |. This function keeps only the data lines, removes separator lines and the header that SAP repeats on every page, and makes duplicate column names unique.

// Name the query fxLoadSapList. Input: the file as binary.
(file as binary) as table =>
let
    // 1252 = Windows ANSI. If names show odd characters, try 65001 (UTF-8).
    Lines = Lines.FromBinary(file, null, null, 1252),
    DataLines = List.Select(Lines, each
        Text.StartsWith(Text.TrimStart(_), "|") and not Text.StartsWith(Text.Trim(_), "|-")),
    Split = List.Transform(DataLines, each
        List.Transform(List.RemoveLastN(List.Skip(Text.Split(_, "|"), 1), 1), Text.Trim)),
    Header = Split{0},
    UniqueHeader = List.Accumulate(Header, {}, (seen, h) =>
        seen & {if List.Contains(seen, h) then h & "_" & Text.From(List.Count(List.Select(seen, each Text.StartsWith(_, h)))) else h}),
    Rows = List.Select(List.Skip(Split, 1), each _ <> Header),
    Result = Table.FromRows(Rows, UniqueHeader)
in
    Result

To use it on one file: fxLoadSapList(File.Contents("C:\Finance\SAP exports\FBL3N_Mar.txt")).

3. Remove subtotal, total and blank rows

ALV exports mark totals with * or ** in the first column. This keeps real lines only.

let
    Source = fxLoadSapList(File.Contents(FolderPath & "\FBL3N_Mar.txt")),
    FirstCol = Table.ColumnNames(Source){0},
    NoTotals = Table.SelectRows(Source, each
        let v = Record.Field(_, FirstCol), t = if v = null then "" else Text.Trim(Text.From(v))
        in not Text.StartsWith(t, "*")),
    NoBlanks = Table.SelectRows(NoTotals, each
        List.NonNullCount(List.Select(Record.FieldValues(_), each _ <> null and Text.Trim(Text.From(_)) <> "")) > 0)
in
    NoBlanks

4. Turn SAP amounts into numbers (trailing minus, Indian or European format)

SAP shows negative amounts with the minus at the end (45,000.00-). The decimal mark follows each user's settings (SU3 → Defaults → Decimal notation). Tell the function which mark your export uses.

// Name the query fxSapNumber
(t as nullable text, optional decimalMark as nullable text) as nullable number =>
let
    dm = if decimalMark = null then "." else decimalMark,
    s = if t = null then "" else Text.Trim(t),
    neg = Text.EndsWith(s, "-") or Text.StartsWith(s, "-"),
    core = Text.Remove(s, {"-", " ", if dm = "." then "," else "."}),
    std = if dm = "." then core else Text.Replace(core, ",", "."),
    n = if std = "" then null else Number.FromText(std, "en-US")
in
    if n = null then null else if neg then -n else n

Apply it to a column:

Table.TransformColumns(NoBlanks, {{"Amount in local cur.", each fxSapNumber(_, "."), type number}})

1,25,000.00- becomes -125000, and with "," as the mark, 1.234,50- becomes -1234.5.

5. Read dd.mm.yyyy dates

Table.TransformColumns(Data, {{"Posting Date", each if _ = null or _ = "" then null
    else Date.FromText(_, [Format = "dd.MM.yyyy", Culture = "en-IN"]), type date}})

On older Excel versions that do not accept the Format option, use Date.FromText(_, "de-DE"), which reads 31.03.2026 correctly.

6. Combine every monthly file in a folder

Save each month's export into one folder. New files are picked up on Refresh.

let
    Files = Folder.Files(FolderPath),
    TextFiles = Table.SelectRows(Files, each Text.Lower([Extension]) = ".txt" and not Text.StartsWith([Name], "~")),
    WithData = Table.AddColumn(TextFiles, "Data", each fxLoadSapList([Content])),
    Keep = Table.SelectColumns(WithData, {"Name", "Data"}),
    Combined = Table.ExpandTableColumn(Keep, "Data", Table.ColumnNames(Keep{0}[Data]))
in
    Combined

The Name column tells you which file each line came from, which helps when a figure needs tracing.

7. Debit and credit indicator to signed amounts

Some exports show positive amounts with a separate debit/credit column: S (Soll) is debit and H (Haben) is credit.

Table.AddColumn(Data, "Signed amount", each if [#"D/C"] = "H" then -[Amount] else [Amount], type number)

8. Keep leading zeros on vendor, customer and G/L numbers

SAP stores numeric vendor numbers with leading zeros (0000100023). Excel drops them, so lookups fail. Set the column type to text, then pad it.

Table.TransformColumns(Data, {{"Vendor", each Text.PadStart(Text.From(_), 10, "0"), type text}})

9. Indian financial year and period from the posting date

let
    WithFY = Table.AddColumn(Data, "FY", each
        let d = [Posting Date], y = Date.Year(d) - (if Date.Month(d) < 4 then 1 else 0)
        in "FY" & Text.From(y) & "-" & Text.End(Text.From(y + 1), 2), type text),
    WithPeriod = Table.AddColumn(WithFY, "FY period", each Number.Mod(Date.Month([Posting Date]) + 8, 12) + 1, Int64.Type)
in
    WithPeriod

April gives period 1 and March gives period 12. Special periods 13 to 16 exist only in the SAP period field, not in the date.

10. Unpivot a period-wise balance report

Balance reports such as FAGLB03 or a report-painter layout often have one column per period. Unpivot them into rows so that you can pivot freely.

Table.UnpivotOtherColumns(Data, {"G/L Account", "Description"}, "Period", "Balance")

11. Map G/L accounts to reporting groups and flag gaps

Keep a two-column mapping table (GL, Group) in the workbook and load it as a query named Mapping.

let
    Merged = Table.NestedJoin(Data, {"G/L Account"}, Mapping, {"GL"}, "Map", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Map", {"Group"}),
    Flagged = Table.AddColumn(Expanded, "Mapping check", each if [Group] = null then "UNMAPPED" else "OK", type text)
in
    Flagged

Filter UNMAPPED before you publish anything. A new G/L opened during the month is the usual cause. For Schedule III mapping, see template Y.

12. Remove duplicate lines after combining files

If two exports overlap, the same document line appears twice. An SAP accounting line is unique by company code, document number, fiscal year and line item.

Table.Distinct(Data, {"Company Code", "Document Number", "Fiscal Year", "Line item"})

Good habits

  • Keep the raw export untouched in its folder. Power Query reads it and never changes it.
  • Rename every step (right-click → Rename) so that a reviewer can follow the logic.
  • Add a row count check: compare Table.RowCount with the count SAP shows at the bottom of the report.
  • Column names follow the SAP user's language and layout. If a colleague's export has different headings, save and share one ALV layout (for example /R2R_MONTH).
  • Exports can hold personal data (employee vendors, bank details). Store them only where your company's policy allows.

SAP, FBL1N, FBL3N and the other names here are trademarks or transaction codes of SAP SE. This site is not affiliated with SAP SE. Test every query on a copy before you rely on it.

For computation and reconciliation assistance only; not a substitute for formal statutory audit or legal tax advice. Rules, system versions and your own policies differ: check them before you rely on this guide.

Last reviewed 25 October 2026. Spotted an error? Tell us and we will correct it.