Records with an issue, by branch file
Chart data
| Branch | Duplicates | Missing values | Formats | Total |
|---|---|---|---|---|
| Poznań | 7 | 10 | 57 | 74 |
| Łódź | 13 | 12 | 44 | 69 |
| Lublin | 8 | 6 | 51 | 65 |
| Kraków | 9 | 11 | 42 | 62 |
| Wrocław | 7 | 12 | 22 | 41 |
| Gdańsk | 6 | 3 | 27 | 36 |
| Katowice | 4 | 4 | 18 | 26 |
| Warszawa | 4 | 6 | 11 | 21 |
Records with an issue, by branch file
Chart data
| Branch | Duplicates | Missing values | Formats | Total |
|---|---|---|---|---|
| Poznań | 7 | 10 | 57 | 74 |
| Łódź | 13 | 12 | 44 | 69 |
| Lublin | 8 | 6 | 51 | 65 |
| Kraków | 9 | 11 | 42 | 62 |
| Wrocław | 7 | 12 | 22 | 41 |
| Gdańsk | 6 | 3 | 27 | 36 |
| Katowice | 4 | 4 | 18 | 26 |
| Warszawa | 4 | 6 | 11 | 21 |
Monthly report from branch files
- Problem
- Every month 8 branches send files in different formats, and the report is assembled by hand.
- Data
- 8 CSV files, 5,547 rows with typical errors: duplicates, missing amounts, mixed date and number formats. The same approach works with .xlsx files.
- Work
- A Power Query query reads the whole folder, standardises formats and removes duplicates. A control sheet reconciles totals with each file, and a VBA macro saves each branch’s report as PDF.
- Result
- 5,425 valid rows in one table, 58 duplicates removed, 272 entries standardised, 64 records listed for follow-up. Control totals match for 8 of 8 branches.
- Tools
- Power Query, VBA, Excel
Sample input files: branch file: warszawa, branch file: krakow, branch file: gdansk