I sourced the data from the tables published as annexes to the ICAO Annual Reports of the Council (and/or “World of air transport in year YYYY”). These were downloaded from the ICAO public website. The oldest edition with data available was from 2006, which would contain data down to year 1998. I downloaded 4 different reports – 2006, 2013, 2016 and 2022.
I used the Power Query – Get data – From PDF to extract the relevant tables from each report, then selected Transform data.
I cleaned the first rows and appended 4 queries (4 tables) together. Since some of the figures for some years overlapped, I used Remove Rows – Remove duplicates. The annual figures for some of the years were not exactly matching, but the difference was insignificant for the purposes of this report.
The appended data required some cleaning. There were some inconsistencies between the appended rows, such as having a blank space between thousand of measurements and inconsistent use of decimal separator. To remove the empty spaces in thousands figures, I could use Excel with =NUMBERVALUE function.
But I remained using Power Query, here I first replaced empty space in values and afterwards converted the data type into the whole number.
I exported the resulting table into a .csv file for easier transportation and importing.