Today’s Excel is NOT the Excel of the past.
It has evolved, and one of its most powerful upgrades is Power Query.
If you are a demand planner drowning in disconnected files, a supply planner juggling multiple formats, or part of an S&OP team chasing data across systems, Power Query can transform the way you work.
This is not an exaggeration. It’s a game changer.
In this article, I will walk you through 7 practical steps to apply Power Query in demand planning, supply planning, materials planning, inventory, and S&OP.
You will be able to start using it right now.
I will focus on real applications you can put into practice immediately.
For step-by-step guidance with screenshots, check out my full blog post [here]
It’s clear, simple, and easy to follow.
Let’s dive in!
Step # 1 – Identify the Pain Points
What to do: Start by listing your recurring Excel nightmares: file chaos, mismatched SKUs, and inconsistent data.
Application Examples:
-Demand planners: Repeatedly merge regional forecasts from different owners.
-Supply planners: Can’t trust the lead times in weekly updates.
Challenge: You are solving the same data issues every cycle.
Fix it: Create a checklist of common issues (e.g., duplicate SKUs, wrong units).
Use this as your guide for Power Query transformations.
Step 2 – Gather Your Data Sources
What to do: List every file you use. For example: forecasts, open orders, inventory snapshots, and label the source and format.
Application Examples:
-Materials planners: BOMs (bills of materials) from SAP, supplier OTIF (on time in full) from supplier portal, purchase order tracking in Excel.
-Inventory analysts: Pull safety stock data and reorder points from different systems.
Challenge: Data is in multiple formats; CSV, Excel, SQL, ERP extracts.
Fix it: Power Query lets you pull from all these sources and unify the structure. Label files clearly and define key fields (e.g., SKU code, location).
Step 3 – Connect to Your Data in Power Query
What to do: Load your files into Power Query directly. No more manual imports.
Application Examples:
-S&OP leads: Connect forecast, supply, and financial files for a unified dataset.
-Demand planners: Pull in actuals from sales systems every week automatically.
Challenge: Manual copy-pasting breaks formulas and introduces errors.
Fix it: Use “Get Data” to connect to local folders, SharePoint, or SQL servers. Set up folder connections to read new files as they arrive.
Step # 4 – Clean and Transform
What to do: Fix column names, remove duplicates, align date formats, and apply business rules (e.g., filter canceled orders).
Application Examples:
-Supply planners: Standardize supplier names and round lead times.
-Inventory: Filter obsolete SKUs, correct unit mismatches (e.g., cases vs units).
Challenge: Inconsistent formatting: SKU_001″ vs. “sku-001”, “Lead Time” in days vs. weeks.
Fix it: Use Power Query tools like Trim, Upper, Replace, Change Type, and “Remove Duplicates.” Save these steps. They refresh automatically.
Step # 5 – Merge Tables for Unified Insights
What to do: Join files by shared fields (e.g., SKU, Supplier ID, Location) using Merge or Append.
Application Examples:
-Demand & supply planners: Merge forecast and capacity to create a constrained view.
-S&OP: Join financial plan with supply chain data to align operations and strategy.
Challenge: You have multiple files but no single view. This leads to misalignment.
Fix it:
-Use Merge Queries to combine related data (e.g., forecast + lead time).
-Use Append Queries to stack time periods (e.g., historical vs. current).
Step # 6 – Set Up Automatic Refresh
What to do: Schedule a refresh so Power Query pulls new data without you lifting a finger.
Application Examples:
-Demand: Always see the latest forecast accuracy or bias.
-Inventory: Safety stock calculations stay up-to-date without rework.
Challenge: Planners waste hours redoing the same steps weekly.
Fix it: Enable auto-refresh in Excel or Power BI. Power Query reapplies all steps, so your dashboard always uses fresh inputs.
Step # 7 – Visualize and Share
What to do: Use Excel pivot tables or Power BI to create charts for forecast vs actual, fill rates, OTIF, safety stock health, and more.
Application Examples:
-S&OP: Share a live dashboard with Sales and Finance.
-Materials planners: Visualize purchase order delays by supplier.
Challenge: Your insights are stuck in Excel rows. No one sees the story.
Fix it: Build visual reports linked to your cleaned dataset. Use slicers and filters to drill by product, region, supplier, or time.
Power Query cleans up your data and your entire planning process.
Start small: try connecting one Excel file and removing duplicates using Power Query.
Once you see how easy it is, you will never go back to manual cleanup again.
