Public sector · local government
Tracking and social-impact analysis platform
From manual extractions to an automated analytical ecosystem with more than 15 Power BI reports.
+15 reports
Interactive in Power BI
10 districts
Territorial network covered
4 sources
Integrated and automatically normalized
Overview
Power BI BI platform for Joves de Barcelona, the City Council's network of youth information and guidance services. It replaced manual extraction and reporting with an automated analytical ecosystem that covers consultations, advisories, activities and specific devices, measuring impact in real time. It includes Python extraction and normalization scripts, Power Query modeling, +15 interactive Power BI reports and automation of the refresh cycle.
Technologies
The client
Network of youth service points of Barcelona City Council, distributed across the ten districts. It offers information, guidance, advisory services (academic, employment, housing, entrepreneurship, mobility), training activities and specific devices. Its application records tens of thousands of interactions per year.
The challenge
The in-house application recorded consultations, advisories and activities, but there was no analytical layer to exploit them. Teams depended on manual extractions, scattered spreadsheets and repetitive cleaning for quarterly and annual reports. The data required strong normalization: inconsistent text, special characters in center names, multi-value fields and different structures per service type. They needed a system that would centralize the sources, automate normalization and put the indicators within reach of every team without technical intervention.
The solution
Data sources and integration
Four integrated sources: registration application (consultations, advisories, activities and calendar in CSV), Google Docs (planning and goals per service), SharePoint (central repository of processed CSVs and source for Power BI) and a configuration Excel with dimensions. All reports work with the same version of the data.
Extraction and normalization scripts (Python)
Python scripts in GitHub that automate data preparation for the four typologies (consultations, advisories, activities and specific extractions): normalization of special characters and encodings, correction of erroneous categorizations, categorical standardization and specific extractions for devices and programs with their own logic.
Normalization and modeling in Power Query
Once the CSVs arrive at SharePoint, Power Query applies a second layer of transformations in Power BI: typing and date conversion, shared dimensions (genders, ages, scopes, modalities, quarters and months in school and calendar order), DAX tables to unpivot records by gender, age and educational level, and relationships between facts and dimensions.
Evaluations and data-cycle automation
Specific module for program evaluation analysis: average score per activity, service and thematic block, and evolution of satisfaction. Automated refresh cycle from Power BI Service: when the scripts run, the CSVs are published to SharePoint and a Refresh updates all reports without manual intervention and from any device.
Results
End of manual processes: extractions, cleaning and data preparation that consumed hours of work each quarterly close were eliminated.
Real-time impact: unified view of the network accessible to all territorial teams from any device.
Multidimensional analysis: by service, team, district, educational center, activity type, gender, age and month, a capability that did not previously exist.
Tailored reports per program: adapted to the registration logic and to the indicators specific to each action and device.
Scalable analytical foundation: ready to incorporate new programs, devices or data sources in future courses.