Skip to main content

Introduction to Excel Tables and Power Query

A practical overview of how to use Excel Tables and Power Query for engineers working with Creo Parametric BOM data.

What Are Excel Tables?​

Excel Tables are structured data ranges that provide:

  • Automatic formatting and column headers
  • Structured references in formulas (e.g., [@Material] instead of D2)
  • Auto-expansion when new rows are added
  • Built-in filtering and sorting

Nitro-BOM exports directly to native Excel Tables, giving you all these benefits automatically.

Benefits for Engineers​

  • Consistent data structure across exports
  • Easy comparison between BOM versions
  • Automatic Power Query refresh when data changes
  • Named ranges that formulas can reference reliably

Introduction to Power Query​

Power Query is Excel's built-in ETL (Extract, Transform, Load) tool. It lets you:

  • Connect to data sources (Excel tables, databases, web APIs)
  • Transform data (filter, pivot, merge, calculate)
  • Load results into new worksheets or data models

Why Power Query for Creo Data?​

  • Compare BOMs across assembly versions
  • Merge BOM data with cost databases
  • Create summary reports that auto-update
  • Filter and reshape data without modifying the source

Connecting to Creo Data​

When Nitro-BOM exports BOM data, it automatically creates Power Query connections. These connections refresh every time you re-export, giving you live dashboards.

Example: Cost Rollup​

  1. Export BOM with Nitro-BOM → creates BOM_Data table
  2. Create a Power Query that joins BOM_Data with your Cost_Database
  3. Load the merged result to a Cost_Summary sheet
  4. Next export automatically updates the cost summary

Practical Examples​

  • BOM Comparison: Use Power Query Merge to compare two BOM exports side-by-side
  • Parameter Audit: Filter for components missing required parameters
  • Cost Estimation: Join BOM quantities with unit costs from an external source
  • Weight Rollup: Sum component weights by sub-assembly level