Requirement
A recruitment company wanted an engaging and informative report showing people’s movement between jobs, sectors, job level and geography, and how it changes over time.
Information from their own placements was supplemented with data from a variety of open sources, and they wanted to use it for market intelligence and marketing purposes. Their spreadsheets were difficult to report from and although they had developed an Excel solution it was very slow and inflexible.
Challenges
Data was manually collected and had errors and inconsistencies that needed to be cleaned up.
Job levels, types, products and other information was all rolled together in a single Job Title field. Job Titles were inconsistent, free format text that had to be split and standardised for reporting.
Source Excel spreadsheets were hosted on SharePoint, and all files in a particular folder were to be loaded irrespective of their file name.
Solution
The source data was supplemented with a reference spreadsheet to standardise parts of the Job Title and to categorise Companies by sector.
A lot of work was done developing the reference data using the Fuzzy Lookup Add-In for Excel, available at https://www.microsoft.com/en-gb/download/details.aspx?id=15011.
We had people’s names but not gender, so used Genderize.io API via the Excel WEBSERVICE function to get a best guess.
A Power Query function was written to count word matches between a standardised field (Job Level for example) and the words in the Job Title. This was a bespoke fuzzy match on the reference table – before fuzzy match became available on Power BI!
To get the Excel files from the folder we used a Power Query technique that reads the complete list of SharePoint contents, filters down on the folder name then gets the content of the files using the linking URL. Note that if SharePoint has a lot of content this approach can be very slow in which case it’s advisable to load each file individually by name.
Design Highlights
This project involved a lot of work in Excel as well as Power BI. An innovative approach was needed to split the free format Job Title into standardised fields.
The focus of this report is the movement of people between things – job level, role, geography and sector – so it lends itself to the Chord Chart. We think it’s particularly elegant . . .