I recently got hooked on Tableau Public. It’s pretty awesome!
One of the downsides, however, is the need to find yourself some interesting “open” datasets to visualize.
As the number of prospective students at British universities has long been of interest to me, I thought I’d check out if UCAS had any publicly available data to crunch
Luckily for me, they did
Yet, the data format of those files was not what I would call an analyst delight – there were 6 extra rows on top of each dataset, which even the Data Interpreter in Tableau Public couldn’t handle
If I were to make a comprehensive analysis out of this data, I would have to spend at least an hour on data preparation – i.e. getting rid of the 6 extra lines from each of the 103 files
Good that I’ve got some VBA tricks up my sleeve!
After using Power Query to generate a list of all the files saved in that folder, I fired up the following code, which would loop through every workbook on the list, open it, delete the first 6 rows and save it
You can find the interatctive workbook here https://public.tableau.com/views/ApplicationstoBritishEducation/Overview
Happy VBA coding!