// Weekly report: every export in one folder -> one clean, de-duplicated table.
// From botlery.com/blog/automate-weekly-excel-report.html
let
    FolderPath = "C:\Reports\Weekly exports\",
    LinesAboveHeader = 2,    // title lines your system writes above the header row

    Files = Folder.Files(FolderPath),
    CsvFiles = Table.SelectRows(Files, each Text.Lower([Extension]) = ".csv"),
    // Newest export first. Works when file names carry the date (orders-2026-09-28.csv);
    // otherwise sort on "Date modified" instead of "Name".
    NewestFirst = Table.Sort(CsvFiles, {{"Name", Order.Descending}}),

    ReadExport = (content as binary, fileName as text) as table =>
        let
            // Drop the title lines BEFORE parsing: Csv.Document takes its column count from the
            // first line it reads, so a one-field title line would squeeze every row into one column.
            AllLines = Lines.FromBinary(content, QuoteStyle.Csv, false, 65001),
            DataLines = List.Skip(AllLines, LinesAboveHeader),
            Raw = Csv.Document(Text.Combine(DataLines, "#(cr,lf)"), [Delimiter = ",", QuoteStyle = QuoteStyle.Csv]),
            Promoted = Table.PromoteHeaders(Raw, [PromoteAllScalars = true]),
            Trimmed = Table.TransformColumnNames(Promoted, Text.Trim),
            Tagged = Table.AddColumn(Trimmed, "Source File", each fileName, type text)
        in
            Tagged,

    AllRows = Table.Combine(Table.AddColumn(NewestFirst, "Data", each ReadExport([Content], [Name]))[Data]),

    // Real orders only: no blank lines and no "Total" line at the bottom.
    OrdersOnly = Table.SelectRows(AllRows, each [Order ID] <> null and [Order ID] <> ""
        and not Text.StartsWith(Text.Lower([Order ID]), "total")),
    Typed = Table.TransformColumnTypes(
        OrdersOnly,
        {{"Order ID", type text}, {"Order Date", type date}, {"Customer", type text},
         {"Region", type text}, {"Amount", type number}},
        "en-US"
    ),
    Tidy = Table.TransformColumns(Typed, {
        {"Customer", each Text.Trim(Text.Clean(_)), type text},
        {"Region", each Text.Trim(Text.Clean(_)), type text}}),

    // Exports overlap, so one order can appear in two files: keep the newest copy.
    // Table.Buffer holds the newest-first order so Table.Distinct keeps the right row.
    Latest = Table.Distinct(Table.Buffer(Tidy), {"Order ID"}),
    WithWeek = Table.AddColumn(Latest, "Week Starting",
        each Date.StartOfWeek([Order Date], Day.Monday), type date),
    Sorted = Table.Sort(WithWeek, {{"Order Date", Order.Ascending}, {"Order ID", Order.Ascending}})
in
    Sorted