How We Streamlined Customer Forecast Data for Production Planning

The pipeline starts with the files themselves. On every forecast cycle, our team downloads the latest forecasts for each trading partner from SPS Commerce, using access the client provides. A Python script then puts every customer's file into one consistent, predictable format, and each file is uploaded to its partner's folder in a dedicated SharePoint site.

1. Download from SPS CommerceIn the transactions search, our team picks one trading partner and its plants, filters to forecast documents in a recent date range, ticks the latest versions and downloads them as CSV files.

1. Standardise with a Python scriptEvery customer exports its forecast differently: extra header rows, multi-record layouts, dates written in different ways, one file or several per plant. A Python script does the heavy lifting with a small rule set for each trading partner, and writes clean files for each partner in a consistent format, cycle after cycle.
2. Name by forecast dateThe script names each file with the date the forecast was issued, for example PartnerA_forecast_2026-10-12.csv. The model reads the date from the name, so nobody has to type it anywhere.
3. Upload to the partner's folderThere's one folder per trading partner. Earlier versions stay where they are, and that is what builds the history.
4. Confirm the upload to the data teamA second Python script counts the files for each trading partner and lists every file's name and size. Our team emails the result to the primary contact in the client's data team, as a final check before the scheduled refresh.
5. Keep the reference data alongsideThe reference table (Part), which maps each customer part number (SKU) to the internal part number and part family, and a location-to-country list live in the same site. The client's data team maintains both.

Python: standardising a partner's files, simplified

from pathlib import Path
import pandas as pd

# One small rule set per trading partner
RULES = {
    "PartnerA": {"skip": 2, "fmt": "%m/%d/%Y",
                 "dates": ["Forecast Start Date"]},
    "PartnerB": {"skip": 0, "fmt": "%Y%m%d",
                 "dates": ["Ship Date"]},
}

def standardise(partner, downloads, forecast_date, out_dir):
    rule = RULES[partner]
    # one or more downloaded files per partner
    frames = [pd.read_csv(f, dtype=str, skiprows=rule["skip"])
              for f in downloads]
    data = pd.concat(frames, ignore_index=True).dropna(how="all")
    data.columns = data.columns.str.strip()   # predictable headers

    for col in rule["dates"]:                 # one date format
        data[col] = (pd.to_datetime(data[col], format=rule["fmt"])
                       .dt.strftime("%Y-%m-%d"))

    name = f"{partner}_forecast_{forecast_date}.csv"
    data.to_csv(Path(out_dir) / partner / name, index=False)

A confirmation before the refresh deadline

Power BI refreshes on a schedule, and the alert flow runs about an hour later. Both only see what is in SharePoint at that moment, so the upload has a deadline: every file has to be in place before the refresh starts. Once the upload is done, a second Python script counts the files for each trading partner and lists every file's name and size, and our team sends the result to the primary contact in the client's data team.

It takes seconds to read, and it catches problems while there's still time to fix them. A partner with zero files, or a file far smaller than usual, stands out before the refresh rather than in the alert email afterwards.

Python: the confirmation report, simplified

import os

TRADING_PARTNERS = ["PartnerA", "PartnerB", "PartnerC", "PartnerD",
                    "PartnerE", "PartnerF", "PartnerG"]

def get_files_by_partner(folder):
    """File names and sizes for each trading partner in the folder."""
    details = {partner: [] for partner in TRADING_PARTNERS}
    for root, _, files in os.walk(folder):
        for file in files:
            size_kb = os.path.getsize(os.path.join(root, file)) / 1024
            for partner in TRADING_PARTNERS:
                if partner.lower() in file.lower():
                    details[partner].append((file, f"{size_kb:.2f} KB"))
    return details

def generate_email(details, dt):
    """Confirmation email: a count per partner, then every file."""
    body = (f"Subject: Demand forecast file report - {dt}\n\n"
            "Dear Team,\n\nPlease find below the details for the "
            f"folder with date {dt}:\n\n")
    for partner, files in details.items():
        noun = "file" if len(files) == 1 else "files"
        body += f"🔹 {partner}: {len(files)} {noun}\n"
        if files:
            body += "    📂 Files:\n"
            for name, size in files:
                body += f"      - {name} ({size})\n"
        body += "\n"
    return body + "Best Regards,\nAutomated Report System\n"

Why SharePoint is the right landing zone

We could have pushed files straight into a database. For a file-based process owned by business users, SharePoint gave us more:

• Every forecast version, keptEach file stays in its folder, so the model holds every past version. That is the foundation of the history described above.
• Add a partner without a rebuildA new trading partner means a new folder, one new rule set in the script and one new query. Nothing downstream changes.
• Maintained by the businessThe data team updates the reference table in Excel, in SharePoint, without ever opening Power BI.
• Easy collaborationOur team, the data team and the planners all open the same CSV files, from SPS Commerce download to Power BI source, so everyone sees exactly what the report is built on.
• No gateway, no stored passwordsSharePoint Online is a cloud source, so the Power BI service refreshes it with organizational sign-in and no on-premises gateway.
• An audit trail for freeEvery file is a record of what each customer sent, kept separate from the logic that interprets it.

This is the heart of the solution. The model has to turn a pile of differently shaped files into one question: how did each customer's view of the coming months change since its previous forecast? Four problems stood between the raw files and that answer.

Problem 1: many partner files, one table

Because the Python script has already done the heavy lifting, Power Query only has light work left. Each partner folder has its own short query that does four things: a light clean-up, renaming columns to the common names, adding a Trading Partner column taken from the file name so every row knows which customer it came from, and giving the table exactly the same columns and order as every other query. Where a partner dates its forecast buckets differently, its query also shifts the start date so every partner lines up on the same calendar.

That identical structure is what makes the next step possible: one append stacks every partner into a single forecast table.

Power Query (M), simplified

// Each partner query: trading partner from the file name...
#"Added Partner" = Table.AddColumn(#"Kept Columns", "Trading Partner",
    each Text.BeforeDelimiter([Source.Name], "_"), type text),

// ...common column names...
#"Renamed Columns" = Table.RenameColumns(#"Added Partner", {
    {"Buyer Part", "SKU"},
    {"Forecast Qty", "QTY"},
    {"Forecast Start Date", "Start Date"}
}),

// ...and the same columns, in the same order, as every other query
#"Same Structure" = Table.SelectColumns(#"Renamed Columns", {
    "Trading Partner", "SKU", "Location", "QTY", "Start Date", "Source.Name"
}),

// Identical structures, so one append builds the forecast table
Source = Table.Combine({PartnerA, PartnerB, PartnerC, PartnerD, PartnerE}),
#"Removed Errors" = Table.RemoveRowsWithErrors(Source)

Problem 2: which forecast version is this row from?

Each file holds forecast quantities by delivery date, but the model also needs to know which version a row belongs to. We made the file name the source of truth. A calculated column reads the ten-character date just before the file extension, so every row is stamped with its forecast date the moment it loads.

DAX calculated column

Forecast Date =
VAR FileName = TRIM ( 'OEM forecast Table'[Source.Name] )
RETURN
    // "PartnerA_forecast_2026-10-12.csv" → 12 Oct 2026
    DATEVALUE ( MID ( FileName, LEN ( FileName ) - 13, 10 ) )

Problem 3: customer part numbers (SKUs) aren't internal part numbers

Customers forecast against their own part numbers (SKUs), and the manufacturer plans against its own. The reference table (Part), an Excel workbook in SharePoint that the client's data team maintains, maps each SKU to its internal part number and part family, and becomes the Part dimension. Plant and location tables add the plant name and country, and a calendar table with a rolling next-six-months flag keeps every view focused on the planning horizon. Kit part families are filtered out of the part-level pages and alerts.

Problem 4: comparing any two forecast versions

Two small calculated tables, Forecast Versions and Previous Versions, list every forecast date and have no relationships at all. They feed two slicers, and the measures read the selections. By default the current version is the latest file and the previous version is the one before it, but a planner can pick any earlier version to compare against. This is where the stored history pays off.

DAX, names simplified

Previous Forecast Demand =
VAR CurrentVersion =
    SELECTEDVALUE (
        'Forecast Versions'[Forecast Date],
        MAX ( 'Forecast Versions'[Forecast Date] )
    )
VAR VersionBefore =
    CALCULATE (
        MAX ( 'OEM forecast Table'[Forecast Date] ),
        FILTER (
            ALL ( 'OEM forecast Table' ),
            'OEM forecast Table'[Forecast Date] < CurrentVersion
        )
    )
VAR Compared =
    SELECTEDVALUE ( 'Previous Versions'[Forecast Date], VersionBefore )
RETURN
    CALCULATE (
        SUM ( 'OEM forecast Table'[QTY] ),
        'OEM forecast Table'[Forecast Date] = Compared
    )

The headline cards compare the two versions over three fixed windows of 30, 90 and 120 days, each starting on the first day of the month after the current forecast. Fixed windows keep the comparison fair: both versions are judged on exactly the same future dates.

Three levels of detail, one layout

The report opens on a home page that shows when the data last refreshed, then offers three analysis pages with the same layout. Planners start broad and drill down: trading partner, then part family, then internal part number. Because every page looks the same, nobody has to relearn where things are.

A report only helps if someone opens it. The alert flow makes sure the important changes reach the team either way. It runs about an hour after each scheduled refresh, and instead of exporting visuals it asks the published semantic model direct questions in DAX, using the Run a query against a dataset action of the Power BI connector.

1. RecurrenceScheduled to match the forecast cycle, about an hour after the refresh.
2. Run a query against a dataset, four timesFour DAX queries run against the published semantic model, one per check.
3. Parse JSONEach result is parsed so its columns can be used in later steps.
4. Create HTML table and ComposeEach result becomes a bordered HTML table.
5. Send an email (V2)One email to the planning team and the data team, with all four tables and a link to the reference table workbook.

Four checks on every new forecast

What the alert email looks like

All four checks land in one email, always in the same order, so readers know exactly where to look. The numbers below match the markers in the sample.

1. Subject and recipients. A fixed subject, sent automatically to the planning team and the data team after each refresh.
2. Link to the reference table. One click opens the Excel workbook where the data team adds missing mappings.
3. Missing part numbers. Each customer part number (SKU) not yet in the reference table, with the trading partner that sent it.
4. Trading partner changes. Partners whose total demand moved more than ±15%.
5. Part family changes. Families that moved more than ±20%, kit families excluded.
6. Part number changes. Internal part numbers that moved more than ±20%, kit parts excluded.

Every change table has the same columns: current forecast, previous forecast, change in % and the difference in units, sorted by the largest difference first.

The queries behind the email

Because the queries call the same measures as the report, the email and the dashboard can never disagree. Changing a threshold is a one-line edit in the flow.

DAX query: trading partner changes beyond ±15%

EVALUATE
FILTER (
    SUMMARIZE (
        'OEM forecast Table', 'OEM forecast Table'[Trading Partner],
        "Current Forecast", [Current Forecast Demand],
        "Previous Forecast", [Previous Forecast Demand],
        "Change", ROUND ( [% Chg] * 100, 2 ),
        "Diff", [Current Forecast Demand] - [Previous Forecast Demand]
    ),
    [Change] > 15 || [Change] < -15
)
ORDER BY ABS ( [Diff] ) DESC

Catching part numbers that aren't mapped yet

When a customer starts forecasting a part number (SKU) that isn't in the reference table (Part), its demand still counts in the trading partner totals, but it has no internal part number or part family, so it drops out of the part-level pages. The fourth query catches this with a set difference: every SKU in the forecasts, minus every customer part number already in the reference table.

DAX query: unmapped SKUs

EVALUATE
VAR Unmapped =
    EXCEPT (
        VALUES ( 'OEM forecast Table'[SKU] ),
        VALUES ( Part[Customer P/N] )
    )
RETURN
    SUMMARIZE (
        FILTER (
            'OEM forecast Table',
            'OEM forecast Table'[SKU] IN Unmapped
        ),
        'OEM forecast Table'[SKU],
        'OEM forecast Table'[Trading Partner]
    )

The email lists each unmapped SKU with its trading partner and links to the reference table workbook. The client's data team fills in the internal part number and part family, and from the next refresh that part's demand rolls up correctly.