Solution-Driven Test Automation: Using Power Query and SQL to Streamline Software Release Testing

The Vertex Payroll Tax product ensures precise compliance for city-level taxes, such as Seattle’s Social Housing Tax. This 5% tax applies to businesses on employee compensation exceeding $1 million annually within Seattle.

Project Challenge

Manually creating testing spreadsheets is inefficient and error-prone. By automating data pulls from the database and centralizing results in Excel, I eliminated manual input, enabled real-time updates as tests are executed in the internal testing tool, and provided flexibility to adjust queries as requirements evolve. Excel’s features support advanced analysis and rapid issue identification.

Automation Solution

Developed a custom Excel workbook using SQL queries to pull live test data on gross IDs, tax amounts, rates, and statuses. 

Power Query merges static reference tables for readable descriptions, enabling one-click refreshes and real-time updates.

• SQL: Extracts comprehensive test records from the database.

• Power Query: Merges, transforms, and optimizes static data for performance.

• Excel: Consolidates tests for tracking pass/fail rates and tax calculation analysis.

Key Benefits

• Eliminates manual input
• Enables real-time updates during test execution
• Provides flexibility to adjust queries as requirements evolve
• Supports advanced analysis and rapid issue identification
• Saves time, reduces errors, and scales for future testing projects
• Streamlines my daily research-to-development workflow at Vertex

#PayrollTax #Automation #Vertex #PowerQuery #SQL #Excel #TaxCompliance #SeattleTax