Airline Customer Review — Power BI

8 min readMay 30, 2023

This is the third post on the recent project I’ve been working on. Check out the second post where we discussed how to do web scraping and get the data that we’re going to use in the step of the project.

In this post, I’ll explain a quick step-by-step on how to create the Power BI dashboard below.

All posts from this series:

Airline Customer Review — Insights
Airline Customer Review — Web Scraping
Airline Customer Review — Power BI [This post]

Loading the data

Since we have already merged all the datasets into one .csv file in the previous post, now we need to load it inside Power BI.

To do that follow these steps:

On the Home tab of your header click on Transform data, this will open the Power Query
In the Power Query’s Home tab click on New Source and choose Text/CSV and select your .csv file
Change the Data Type Detection to be Based on the entire dataset, normally we want to avoid this, but this dataset isn’t big enough to cause problems
Click on OK and right-click on the created table to rename it to factReviews since this will be our fact table

Dimension Tables

To those not familiar with the concept of fact and dimension table I recommend this article. In this step, we’re going to create the dimension tables needed, let’s start by creating the aircraft dimension:

On Power Query, right-click on the factReviews table and duplicate it. Rename the duplicated table to be dimAircraft
In the dimAircraft table right click on the aircraft columns, and select Remove Other Columns. Next, do the same to Remove Duplicates
Next, go to the Add Column tab and add an Index Column From 1. Rename the created column to be aircraftID
Do similar steps for all the other columns that can be dimension tables: airline_name, country, recommended, seat_type, type_of_traveller, and verified_trip

Merging fact and dimensions

Now that all the dimension tables are created, they can be merged into the factReviews table. Let’s do the following:

Go to the factReviews table and on the Home tab click on Merge Queries
Next, select the column that you want to merge from the factReviews, select the corresponding dimension table, and on the corresponding table select the same column. It’s important to select the columns that represent the same data so Power BI will know how to merge and verify matches
You will notice that a new column has been created, click on the Expand icon and choose to expand only the column ID without using the original column name as the prefix
Right-click on the column corresponding to the expanded ID column and delete it since it’s not necessary anymore. For example: after expanding the aircraftID delete the column aircraft
Do this for all the created dimension tables

Choosing tables and fields to match and merge

Date dimension

An important dimension in basically every project is the date dimension. Let’s start by creating a variable that tells us the current time:

Right-click on Power Query’s left menu and select New Query -> Blank Query
Rename the created query to vNow and add the following code to it:

let
    Source = Date.From(DateTime.LocalNow())
in
    Source

Repeat these steps to create a dimCalendar query and paste the following code:

let
    vDateInit = #date(2000,01,01),
    vDateEnd = Date.EndOfYear(vNow),
    days = Duration.Days(vDateEnd-vDateInit)+1,
    list_dates = List.Dates(vDateInit, days, #duration(1,0,0,0)),
    TableDates = Table.FromList(list_dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(TableDates, "Dates", each
        Table.FromRecords({[
            YearNum = Date.Year([Column1])
        ]})
    ),
    #"Expanded Dates" = Table.ExpandTableColumn(#"Added Custom", "Dates", {"YearNum"}, {"YearNum"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Dates",{{"Column1", type datetime}, {"YearNum", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

This will create a dimCalendar table containing all the dates from the current time until 01/01/2000 and a YearNum column corresponding to the date’s Year.

Now that all our dimensions are created, click Close & Apply in Power Query’s Home tab.

Closing and applying Power Query’s changes

Now let’s create the connection between our factReviews table and the dimCalendar table:

On Power BI’s left menu, click Model view
In this view find the two tables
Drag and drop the Date attribute from the dimCalendar table into the date attribute of the factReviews table

The connection created between the tables

Measurements Table

Now we have our data organized, but we still need to create a Table containing the metrics that we're going to display to our users. Let’s start creating it:

In the Home tab of the Model View click on New Table, once an input is prompted replace “Table =” with “Measurements =” and click enter

In the right menu, right-click on the created Measurements table and select New Measure. Create the following measures:

Reviews (Qtd) = COUNTROWS(factReviews) + 0

Recommendations (Qtd) = CALCULATE([Reviews (Qtd)], factReviews[recommended] = TRUE)

Ratings (Qdt) = CALCULATE([Reviews (Qtd)], factReviews[rating] >= 0)

Detractors (Qdt) = CALCULATE([Reviews (Qtd)], factReviews[rating] >= 0, factReviews[rating] <= 6)

Promoters (Qdt) = CALCULATE([Reviews (Qtd)], (factReviews[rating] >= 9 && factReviews[rating] <= 10))

NPS = IF(
    [Ratings (Qdt)] > 0,
    (([Promoters (Qdt)] / [Ratings (Qdt)]) - ([Detractors (Qdt)] / [Ratings (Qdt)]))*100,
    0
)

Last Update = max(vNow[vNow])

Footnote = 
VAR DATE_NOW = [Last Update]
VAR YEAR = FORMAT(DATE_NOW,"YYYY")
VAR UPDATE_DATE = FORMAT(DATE_NOW,"DD/MM/YYYY")
VAR COPY = "Copyright © "&YEAR&" <Your Name> - All Rights Reserved  |  Dashboard Updated at "&UPDATE_DATE

RETURN
COPY

Creating the Visuals

Let’s get back to the Report View by clicking on it in the left menu. Let’s start by adding our background.

Select the right icon in the Visualization menu on the right side of the Report View
Choose the Canvas Background, make it fit the report view, and reduce Transparency to 0

You can add the remaining Dahboard’s images and texts by clicking on the following icons and positioning them wherever needed:

Creating the Cards

Let’s create the first card (Reviews) and the remaining will follow the same idea:

On the Visualizations tab in the right menu click on Card and position it where you need it on the screen
Select the created component and add the card’s measurement by dragging and dropping the Reviews (Qtd) measurements previously created on the Measurements table
Play around and change the card’s visuals as you need

Creating the Charts

Let’s create the first card on the right side of the dashboard (Seat Type) and the second one will follow the same idea. We’re going to have a separate topic for the Average rating per category chart once this has some specific needs.

In the visualizations now click on the Stacked column chart and position it where you need it on the screen
Select the created component and let’s add the measures to it. On the x-axis we want to display the seat type, so drag and drop the seat_type attribute from the dimSeatType table. On the y-axis we want to count how many seat types are there per category, so drag and drop the Reviews (Qtd) measurements previously created
Play around and change the chart’s visuals as you need. The hex value chosen to display the column’s color is #5C6FE6

Creating the Final Chart

Since the Average rating per category chart is displaying the average for each value, we’re going to need to do a workaround to get it since Power BI doesn’t make this option easily available. Since you got to almost the end of this post, you probably already know how Power BI works, so I’m not going to use images to explain the necessary steps.

On Power Query duplicate the factReviews table and rename the created table to factCategory
Removed almost all the Applied Steps from the created table, the only changes that we are going to keep from the original table are the merge and expansion of verifiedTripID, seatTypeID, and travellerTypeID
Remove all the columns from the table, except by the dimensions previously cited, the date, and the fields that we want to calculate the average
Multi-select the fields to calculate the average, right-click and choose the option Unpivot Columns
Filter the Value column to contain only the positive values, since the negative values are not valid as explained in the previous post
Click on Close & Apply

Once this table is created, on Report View do the following:

Create a new Clustered bar chart and position it where you need it on the screen
Select the created component and let’s add the measures to it. On the x-axis we want to display the average of each category, so drag and drop the Value attribute from the factCategory table and choose to show the Average instead of the Sum. On the y-axis we want to display the categories, so drag and drop Attribute from the same table
Play around and change the chart’s visuals as you need

Creating the Filters

For the filters, we’re going to create the first filter (Airline) and the other will follow the same idea:

In the Visualizations panel create a Slicer and position it where you need it on the screen
Select the created component and drag the airline_name from the dimAirline table into the slicer Field
In the same panel select the second item and change the Slicer settings to display a Dropdwon instead of a list
Play around and change the slicer’s visuals as you need

Conclusion

In this post, we covered how to create a Power BI Dashboard using the data collected in the previous post allowing us to get useful insights and be able to tell airlines which services need to be improved.

The developed Dashboard is available on my GitHub.