How to automate a weekly Excel report with Power Query and one button

To automate a weekly Excel report, save each export into one folder and let Power Query combine and clean them: skip title lines, drop the total row, fix types and keep one copy of each order. Build the pivot report on that table, then add a one-button VBA macro that refreshes everything, stamps the time and saves a PDF. The code is free below. Botlery builds Excel automations for a free fixed-price quote.

Updated October 2026 · 9 min read · By Rohail Nisar, founder of Botlery

The weekly report that takes a morning usually follows the same steps every time: export from the system, paste under last week’s data, delete the title rows and the total line, fix the dates that came in as text, spot the orders that appear in two exports, refresh the pivot, save a PDF. Every one of those steps can be written down once. The result is a report that is the same every week, including in the weeks someone else runs it.

What you need

• Desktop Excel for Windows with Power Query, which is built in from Excel 2016 onwards and in Microsoft 365 (Data › Get Data). The macro step does not run in Excel for the web.
• A system that can export orders, invoices or sales as CSV, with the same columns each week.
• One folder that holds every weekly export and nothing else.

To follow along with the same numbers shown below, download the sample files:

• orders-2026-09-21.csvFirst weekly export: two title lines, four orders, a Total line.
• orders-2026-09-28.csvThe next export. It overlaps the first and corrects the amount on order SO-1044.
• weekly-report-query.txtThe Power Query below, as text.
• weekly-report-macro.basThe macro below, ready for File › Import File in the VBA editor.

Step 1: give the exports one home

Save every export into the same folder, for example C:\Reports\Weekly exports\, and keep the date in the file name in year-month-day order (orders-2026-09-28.csv). The query uses the name to decide which export is newest. Never edit the exports themselves: if a figure is wrong, it gets fixed in the query or at the source, so next week’s run does not undo it.

Step 2: the Power Query that combines and cleans

In a new workbook choose Data › Get Data › From Other Sources › Blank Query, open Advanced Editor, replace everything with the query below and change FolderPath. Name the query Orders.

Power Query (M) · Advanced Editor

// 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

What each step fixes

• Lines.FromBinary and List.Skip remove the title lines many systems write above the header row, before the file is parsed. That order matters: Csv.Document takes its column count from the first line it reads, so parsing first and skipping afterwards would squeeze every row into one column. Set LinesAboveHeader to 0 if your export starts with the headers.
• The OrdersOnly filter drops blank lines and the “Total” row at the bottom. If that row stayed in, the pivot would count every week twice.
• Table.TransformColumnTypes with "en-US" reads dates and numbers the way the file writes them, whatever your own Windows settings are. If your system writes dates as 28/09/2026, change it to your locale, for example "en-GB".
• Table.Distinct on Order ID keeps one copy of an order that appears in two exports, and because the files are read newest first, it keeps the newest version. Table.Buffer is there because Power Query does not otherwise promise to keep the sort order when it removes duplicates.
• Week Starting gives every order the Monday of its week, which is what the pivot groups by.
• Source File stays as a column, so any number in the report can be traced to the export it came from.

Step 3: load it and build the report once

Choose Close & Load To › Table on a new sheet. Then build the report the usual way: Insert › PivotTable from the Orders table, on a sheet called Report, with Week Starting in Rows, Region in Columns and Sum of Amount in Values. Add a chart if you like. With the two sample files the report should show:

Six orders, not eight: SO-1043 and SO-1044 are in both exports and are counted once, and SO-1044 shows the corrected 80.25 from the newer file rather than 75.25. Adding the two exports’ own Total lines (821.75 + 680.65) would have overstated the fortnight by 485.25, the double-counted orders. That is the “always slightly wrong” that hand-built reports suffer from.

Step 4: the one-button macro

Next week, the only manual step left is saving the new export into the folder. The macro does the rest in the right order: refresh the query, then the pivots, then write the time on the report and save it as a PDF.

1. Press Alt+F11, choose Insert › Module and paste the code below (or use File › Import File with the downloaded .bas file).
2. Check the two settings at the top: the report sheet’s name and the cell for the time stamp.
3. Save the workbook as Excel Macro-Enabled Workbook (.xlsm). Saved as a normal .xlsx, the macro is dropped; Excel warns you, but the warning is easy to click past.
4. Add the button: Developer › Insert › Button (Form Control), draw it on the Report sheet and pick RefreshWeeklyReport. (No Developer tab? File › Options › Customize Ribbon, tick Developer.)

VBA · module WeeklyReport

Option Explicit

' One button: refresh the queries, then the pivots, stamp the time, save a PDF.
' From botlery.com/blog/automate-weekly-excel-report.html
' Excel for Windows. Save the workbook as .xlsm.

Private Const REPORT_SHEET As String = "Report"   ' the sheet with your pivots and charts
Private Const STAMP_CELL As String = "B2"         ' where "Refreshed ..." is written

Public Sub RefreshWeeklyReport()
    Dim cn As WorkbookConnection
    Dim pc As PivotCache
    Dim pdfPath As String

    On Error GoTo Failed
    Application.ScreenUpdating = False
    Application.StatusBar = "Refreshing the weekly report..."

    ' Make each query finish before the next step. With background refresh on,
    ' RefreshAll returns at once and the pivots would refresh on last week's data.
    For Each cn In ThisWorkbook.Connections
        If cn.Type = xlConnectionTypeOLEDB Then cn.OLEDBConnection.BackgroundQuery = False
    Next cn
    ThisWorkbook.RefreshAll

    For Each pc In ThisWorkbook.PivotCaches
        pc.Refresh
    Next pc

    With ThisWorkbook.Worksheets(REPORT_SHEET)
        .Range(STAMP_CELL).Value = "Refreshed " & Format(Now, "yyyy-mm-dd hh:nn")
        pdfPath = OutputFolder() & "Weekly report " & Format(Date, "yyyy-mm-dd") & ".pdf"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, _
            Quality:=xlQualityStandard, OpenAfterPublish:=False
    End With

    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "Report refreshed and saved as:" & vbNewLine & pdfPath, vbInformation, "Weekly report"
    Exit Sub

Failed:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "The refresh stopped: " & Err.Description, vbExclamation, "Weekly report"
End Sub

' The workbook's own folder, or Documents when the workbook is opened from
' OneDrive or SharePoint (its Path is then a web address, not a folder).
Private Function OutputFolder() As String
    Dim p As String
    p = ThisWorkbook.Path
    If p = "" Or LCase$(Left$(p, 4)) = "http" Then p = Environ$("USERPROFILE") & "\Documents"
    OutputFolder = p & Application.PathSeparator
End Function

The line that matters most is BackgroundQuery = False. With background refresh on, which is Excel’s default for queries, RefreshAll returns straight away and the pivot refreshes on last week’s data while the query is still running. Turning it off makes each step wait for the one before.

Do you need the macro at all?

Not always. If all you want is fresh numbers when the file opens, skip the code: in Data › Queries & Connections, right-click the query, choose Properties and tick Refresh data when opening the file (the pivot table has the same option under PivotTable Options › Data). The macro earns its place when you also want the refresh order guaranteed, the time stamp and the PDF, from one click that anyone can press.

Macros in downloaded files are blocked

Since 2022, Office for Windows blocks VBA macros in files that came from the internet, including email attachments and browser downloads. Windows marks those files (the “Mark of the Web”), and Excel then shows a red SECURITY RISK banner with no Enable Content button. Microsoft’s documented ways to run macros you trust are (Microsoft Learn: Macros from the internet are blocked by default in Office, last updated 17 July 2026, checked 7 October 2026):

• Unblock the file: right-click it in File Explorer, choose Properties, tick Unblock on the General tab.
• Keep it in a Trusted Location: files saved there open with macros enabled (File › Options › Trust Center › Trust Center Settings › Trusted Locations). Use a folder only your team writes to.
• For files on OneDrive or SharePoint: open them with Open in Desktop App rather than downloading a copy, or have your IT admin mark the site as trusted.

This is why this guide gives you the macro as text and not as a ready-made .xlsm: code you paste into your own workbook is not a file from the internet. The block will apply to your colleagues, though. When you email them the finished .xlsm, they will need to unblock it or open it from a shared trusted location.

Pitfalls

• The export changes its headers. A renamed column breaks the type step with an error naming the column. If it happens often, map the headers first, as in our guide to combining files with different headers.
• An export is open in Excel while you refresh. Power Query cannot read a file Excel has locked; close it first.
• Files without dates in their names. Sort on Date modified instead of Name, and do not re-save old exports, or an old file becomes the “newest”.
• Workbooks opened from OneDrive or SharePoint have a web address as their path, where a PDF cannot be saved. The macro then saves the PDF to your Documents folder instead.
• Excel for the web does not run VBA. A report that must refresh without anyone opening desktop Excel needs Office Scripts with Power Automate instead.

Frequently asked questions