Social Services · Victoria

Rebuilding fast, reliable reporting for a multi-site support program

OfficePro Consulting rebuilt a multi-site client data and reporting system that had outgrown Excel. We replaced fragile workbook links and a 360MB+ file with a Power Query consolidation model and a clean row-based structure across four locations, then migrated the existing data into it.

Four locations across Victoria

Hundreds of client records

Power Query · Excel · SharePoint

Discuss your reporting project →

4 locations

Consolidated into one clean dataset

One refresh

Rebuilds all reporting through Power Query

Row-based

New months add rows, not columns

Zero links

No fragile workbook links left to break

The challenge: a 360MB workbook held together by fragile links

Four locations each entered their records into a separate workbook. A central workbook pulled those files together using direct workbook links, then produced the reporting on top. Over time the structure stopped coping.

Four location files

Each site keys records into its own separate workbook

Linked consolidation

A central file pulls all four together with direct workbook links

Links break easily

Horizontal sprawl

Every new month adds columns instead of rows

Thousands of columns wide

Manual rework

Each new breakdown is rebuilt by hand and clients get double counted

Heavy rework every time

Reporting produced

Generated on a file that had grown past 360MB

360MB+

File size, too heavy to open on the reporting machine

Thousands

Columns wide and growing every month

Location workbooks merged by hand

Hundreds

Client records across the program

What we built

A single Excel spreadsheet solution that consolidates every location with Power Query, using a row-based entry template and pivot reporting for the required breakdowns, with the existing data migrated into the new structure.

Power Query Editor — Consolidated Reporting

Home Transform Add Column View Refresh Preview

fx = Table.Combine({#"XYZ — Northbridge", #"XYZ — Riverton", #"XYZ — Eastvale", #"XYZ — Glenmore"})

Queries [6]

Locations

XYZ — Northbridge

XYZ — Riverton

XYZ — Eastvale

XYZ — Glenmore

Append — All Locations

Reporting (Pivot)

No direct links

Date	ABCLocation	ABCService	ABCDemographic	123Count
12/03	Northbridge	Counselling	25–34	2
12/03	Riverton	Casework	35–44	1
13/03	Eastvale	Medical	18–24	1
14/03	Glenmore	Housing	45–54	3

Query Settings

Properties · Name

Append — All Locations

Applied Steps

Source

Changed Type

Reordered Columns

5 columns hundreds of rows No direct workbook links Refresh: 1 action

PivotTable — Reporting

Count of records	Counselling	Casework	Medical	Grand Total
Northbridge	8	3	2	13
Riverton	6	4	1	11
Eastvale	5	2	3	10
Glenmore	4	3	2	9
Grand Total	23	12	8	43

Drag fields between areas to change the breakdown. No new formulas.

PivotTable Fields

Choose fields to add to report

Date

Location

Service

Demographic

Count

Filters

Date ▾

Columns

Service ▾

Rows

Location ▾

Σ Values

Count of records ▾

How it works

Each location keys records into one structured table, one row per record, with controlled dropdowns and validations. The files sit in a SharePoint folder and the central workbook uses Power Query to pull and append every location into a single dataset with no direct workbook links. Pivot tables and slicers sit on top, so breakdowns by demographic, location and service come from changing fields rather than building new formulas. New periods are added as rows, so the data grows down, not sideways.

Delivery phases

Build and test

Against real sample files

Migrate and validate

Totals and exceptions checked

Technologies

Power QueryExcelSharePointPivot Tables

Project snapshot

TypeRebuild and data migration

Locations4

RecordsHundreds

RefreshOne Power Query action

Workbook linksNone

Related services

Spreadsheet Solutions Data Pipelines and Analytics Reporting and Analytics

The results: reporting that scales instead of sprawling

From a 360MB workbook to a structure built to stay small as it grows.

1 refresh

Rebuilds all four locations in one action

Direct links→Power Query

Row-based

New months add rows, not columns

1000s of columns→Grows down

Once only

Each client counted once across periods

Double counted→Single count

Stays light

Quick to open as records grow

360MB+→Lean file

The rebuilt system removes the two problems that were causing the failures. Consolidation runs through Power Query rather than links that break and the data is now row-based, so a new month is a new row instead of widening the file into thousands of columns. Different breakdowns by demographic, location and service come from changing pivot fields rather than rebuilding formulas and a client is counted once across reporting periods. The existing data was migrated and validated against agreed checks, so the move off the old 360MB workbook lost nothing.

Facing a similar challenge?

Tell us what your team is dealing with. We will show you what is possible.

TALK TO US →

Ready to rebuild your reporting?

Tell us where your reporting is breaking down. We'll map out what a rebuild looks like for your reporting, what it takes to deliver and whether it's the right move. No commitment, no sales pitch.

contact@officeproconsulting.com.au

1300 160 336Talk to an Australian consultant

Australia BasedRemote and on-site consulting services available Australia-wide

Thanks. Your enquiry has been sent. We’ll get back to you within 1 business day.