Deployment data sourcing, transformation and cleaning
The souce of the data is https://www.atmmasterplan.eu/depl/essip_objectives/map . I couldnt find a way to download a complete raw data for all the objectives for all the years of the ESSIP/LSSIP process, so I downloaded manually data for the last 2 years for 20 objectives.
40 excel files were saved into a folder.
Using the powerful Power Query – Get data – From file – From folder – Tranform data – Combine files
The souce excel files combined some introductory columns (10). The best way to remove them was to filter out NULL values in the column 2.
Data cleaning – removing the first column and some additional ones, using the first row as headers. Filtering out some of the headers remaining from combining the worksheets.
Since I am keeping the data for 2 years – 2021 and 2022, I need the data for the edition year. These come from the first column, which is based on the imported excel file name, like “eATMPortal_Maptool_AOM13.1_Edition2021_2023-MM-DD.xls”. To extract the Edition year, I used the combination of =MID and =FIND formulas, looking for the information after the third underscore and extracting 11 characters, like this:
=MID(A2,FIND(“_”,A2,FIND(“_”,A2,FIND(“_”,A2)+1)+1)+1,11)
Data cleaned – 1760 rows remained – 20 objectives – 44 States – 2 years.
Objectives additional information – stakeholders, SESAR solutions, ICAO Block Modules
Downloading the data from https://www.atmmasterplan.eu/downloads – Implementation Objectives excel file at the bottom.
Removing some of the sheets which will not be used.
Converting the first column to just leave out just the objective name, using the inbuilt Data – Text to columns function, using the „-„ delimiter. One could also use the combination of the combination of =LEFT and =SEARCH, but it is not necessary since the Text to columns function works great.
Moving the remaing 3 sheets into the deployment data worksheet.
Data modeling
The 3 reports, showing additional information per each objectives ( stakeholders, SESAR solutions, ICAO Block Modules) were modeled in Tableau Data source tab, creating relationship between the objectives names (abbreviations).
