// Combine CSV files whose headers differ: renames every header to one standard
// set BEFORE appending, so "Qty", "Units" and "Quantity" land in one column.
// From botlery.com/blog/power-query-combine-files-different-headers.html
//
// Excel: Data > Get Data > From Other Sources > Blank Query > Advanced Editor,
// paste this whole query, change FolderPath, then Close & Load.
let
    // 1. The folder the files are saved in (keep the trailing backslash).
    FolderPath = "C:\Reports\Sales exports\",

    // 2. The columns you want, in the order you want them.
    StandardColumns = {"Order Date", "Customer", "SKU", "Quantity", "Amount"},

    // 3. Every header spelling seen so far -> its standard name. Left side is the
    //    header as Clean() sees it: lower case, letters and digits only.
    HeaderMap = #table(
        {"Seen", "Standard"},
        {
            {"orderdate", "Order Date"}, {"date", "Order Date"}, {"invoicedate", "Order Date"},
            {"customer", "Customer"}, {"customername", "Customer"}, {"client", "Customer"},
            {"sku", "SKU"}, {"itemcode", "SKU"}, {"productcode", "SKU"},
            {"quantity", "Quantity"}, {"qty", "Quantity"}, {"units", "Quantity"},
            {"amount", "Amount"}, {"total", "Amount"}, {"amountusd", "Amount"}
        }
    ),

    // 4. true = refresh stops and names any header the map does not know yet.
    StopOnUnknownHeaders = true,

    Clean = (name as text) as text =>
        Text.Lower(Text.Select(name, {"a".."z", "A".."Z", "0".."9"})),
    MapRecord = Record.FromList(HeaderMap[Standard], HeaderMap[Seen]),
    Rename = (name as text) as text =>
        Record.FieldOrDefault(MapRecord, Clean(name), Text.Trim(name)),

    // One file: read it, promote headers, rename them, tag every row with the file name.
    LoadFile = (content as binary, fileName as text) as table =>
        let
            Raw = Csv.Document(content, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
            Promoted = Table.PromoteHeaders(Raw, [PromoteAllScalars = true]),
            Renamed = Table.TransformColumnNames(Promoted, Rename),
            Tagged = Table.AddColumn(Renamed, "Source File", each fileName, type text)
        in
            Tagged,

    Files = Folder.Files(FolderPath),
    DataFiles = Table.SelectRows(Files, each Text.Lower([Extension]) = ".csv"),
    Loaded = Table.AddColumn(DataFiles, "Data", each LoadFile([Content], [Name])),
    Combined = Table.Combine(Loaded[Data]),

    Unknown = List.Difference(Table.ColumnNames(Combined), StandardColumns & {"Source File"}),
    Checked =
        if StopOnUnknownHeaders and not List.IsEmpty(Unknown) then
            error Error.Record("Unknown headers", "Add these to HeaderMap: " & Text.Combine(Unknown, ", "))
        else
            Combined,

    Ordered = Table.SelectColumns(Checked, StandardColumns & {"Source File"}, MissingField.UseNull),
    // Drop rows where every standard column is empty (blank lines, ",,,," rows).
    NoBlankRows = Table.SelectRows(Ordered, each not List.MatchesAll(
        Record.FieldValues(Record.SelectFields(_, StandardColumns)), each _ = null or _ = "")),
    Typed = Table.TransformColumnTypes(
        NoBlankRows,
        {{"Order Date", type date}, {"Customer", type text}, {"SKU", type text},
         {"Quantity", Int64.Type}, {"Amount", type number}},
        "en-US"
    )
in
    Typed