Automating a manually prepared Excel report involves more than connecting to a database. Reliable results require agreement on data ownership, field meanings and refresh expectations. An initial requirements record makes both the project scope and ongoing maintenance responsibilities clear.
1. Describe the data flow and business rule
Identify the ERP version and the report or approved interface that will supply data. Define source fields, destination columns, unique record keys, date ranges and currencies. Use examples to explain cancellations, returns and duplicate records. State explicitly whether the initial scope only reads data or also writes back, and confirm the integration method supported by the ERP vendor.
- Prepare masked sample inputs and outputs instead of real personal data.
2. Design access and refresh behavior
Record the server location, network access, authentication method and Excel version. Power Query connector capabilities vary by host product; a Power BI feature should not be assumed available in Excel. Limit reporting access to the permissions it actually needs. Assess connection encryption, certificate trust and source privacy levels, and avoid using an administrator account or a password written into the workbook as a permanent design.
- Define refresh frequency, data volume and busy operating periods.
3. Agree acceptance tests and operation
Define checks that compare output totals with the ERP for the same period. Include date formats, Turkish characters, decimal separators and missing values in test samples. Assign responsibility for source changes, lost connectivity and failed refreshes. Users should be able to recognize stale data, with the last successful refresh time visible. Establish a test environment, business approval and release plan before moving to a production connection.
- Document maintenance ownership, error reporting and change approval.
Official technical sources
- Microsoft Learn — Power Query SQL Server connector
- Microsoft Learn — Security best practices for Power Query
- Microsoft — Import data from data sources with Power Query
This is general information. Version- and environment-specific work needs an authorised technical assessment.