top of page

Travel Guide Comparisons

In this project I gathered 10 country's data that my wife and I are interested in traveling to from the UN's data website. We wanted to make some comparisons on certain metrics,
such as the price of flights, most recent exchange rates, Consumer Price Indexes, population growth rates, and government expenditures on developmental indicators. I also ended up gathering a few outside sources of data to supplement activities that I was interested in, and to update some portions of the original data that seemed to have data entry errors.

Final Product

Travel_Guides_Full_Power_BI.PNG

Part 1: Gathering & Cleaning The Data

I started this project by deciding 10 countries that I was interested in traveling to and viewing data for:

​

  1. Canada (I chose Vancouver for travel purposes)

  2. Germany

  3. Greece

  4. Iceland

  5. Morocco

  6. The Netherlands

  7. New Zealand

  8. Norway

  9. Switzerland

  10. USA (I chose Portland, Oregon for travel purposes)

From Web - Excel.PNG

With Power Query I performed the data cleaning and organization that would be necessary for these data sets to be visualized. My first step was to transpose the original rows and columns pictured to the left in the example Table O. Some of these newly transposed rows in the tables corresponded to specific years (2010, 2015, and 2021), to which I renamed them. Next, I gave the tables and columns specific and consistent names. I repeated this process for all 10 countries on separate excel power queries.

​

Some countries, such as the USA and Norway, recorded a few extra measures regarding
sanitation or drinking water in their "Environment and Infrastructure Indicators" table. I took this into account and added these columns into each of the 10 excel sheets, to avoid potential data loss.

​

This consistent table and column naming had a significant impact on cutting down the time that it took to clean the data in a separate excel Power Query, which involved Excel's "From Folder" Power Query option and all 10 excel files.

USA_Org_Query.PNG

After the initial process of creating all 10 separate excel files was completed, I then began a combined Power Query using the "From Folder" option in excel's data tab. In this query, I handled the majority of the data cleaning. This involved multiple methods of cleaning:

  1. Trimmed for excess spaces

  2. Separated male and female columns by "/" delimiters

  3. Changed the majority of the data types from their original "Text" to Whole Numbers or Decimals

  4. Removed some incorrect values (such as extra periods)

  5. Finalized column names

  6. Added an ID column based on the General Information table for each unique country name

Power BI_Queries.PNG

A few of the data cleaning portions were completed during Part 2, after I noticed some
issues with the visualizations and managing table relationships:

  1. Updated capital city names to work correctly with the Power Bl map

  2. Created abbreviations and expanded names for the countries

  3. Added regions to each country

  4. Created a Key Lookup table based on each country name per specific year

  5. Added an updated exchange rates column in the General Information table for comparison

  6. Updated capital city populations (UN data had some that were incorrect)

  7. Added Attractions, Best Times to Visit, and Flag pictures to the General Information table

An example of some of the final excel tables can be seen below, before they were re-entered into Power Bl.

Key Lookup Table

Combined_Lookup_Table.PNG

General Information / ID Table

General_Information.PNG

Economic Indicators Table (Linked by Key and ID Columns)

Combined_Economic_Indicators.PNG

Part 2: Visualizing The Data

Power BI Page 1 and 2.PNG

In this portion of the project, I decided which metrics I was interested in displaying, and
added some additional data and information to the models that would be helpful in a travel guide. Some of this additional information included the Best Months To Visit, Attractions, Exchange Rates, and Google Flights plane ticket cost information.

Flights Prices.PNG

The blue table pictured, containing average flight costs and date information from Google flights, was added directly into the Power BI Model, and linked to the General Information table based on the unique Country names in this table.

Iceland Google Flights.PNG

This table was created by manually gathering the minimum and maximum values of the average price history (prices displayed on the left) for a 1st and 2nd half of each month. This first half of each month was the 1st - 15th, and the second half of each month was the 16th to 30th or 31st. If my wife and I were to visit these countries, we would most likely take a 1.5 -2 week vacation, and I wanted to make sure that the prices didn't have drastic differences during different times of the month.

Population Parameters.PNG
Developmental Parameters.PNG

For the last portion of this project, I decided to compare Population Indicators and
Developmental Indicators from the UN Data. I was curious to see how metrics such as
Population Growths, Age Distributions, Education Expenditures, and GDP Growth Rates
changed between these countries over an 11 year period. Creating parameters for these indicators was extremely helpful in allowing further drilling down of the data, which was assisted further by the Year and Country filters that I'd made. I did keep in mind that the 2021 data could be skewed for many of these metrics, due to the Covid-19 pandemic.

​

I created some measures for this project as well, to help display the data in a more concise or accurate way. I've listed some of the examples and formulas that I've used below.

​

Here are some examples of the measures used:

  1. Tourism at National Borders - From the Environment and Infrastructure table

    1. I didn't like viewing this original number in the thousands, so I converted these numbers to their full versions:

      1. SumX(Environment_and_Infrastructure, Environment_and_Infrastructure[Tourist / Visitor Arrivals At National Borders (Thousands)] * 1000)

    2. After the above step was completed, I then chose the latest data from 2021 to display:

      1. Calculate([Total Tourism / Visitors at National Border], 'Environment_and_Infrastructure'[Year] = 2021)

  2. Best Months To Visit - From the Google Flights table

    1. Because I included two halves to each month, for average flight price purposes, I split the "number of occurences" in half.

      1. CALCULATE(COUNT(Flights[Country])/2)


Here are some examples of the parameters used:

  1. Population Parameters - For the Population Indicators and Unemployment Rates chart (displayed as the Population Indicators filter)

  2. Developmental Parameters - For the Developmental Indicators and GDP Growth chart (displayed as the Developmental Indicators filter)

bottom of page