LET’S EXPLORE THE
DATA AND THE
FINAL DASHBOARD
LET’S EXPLORE THE WORLD HAPPINESS REPORT DATASET
• The World Happiness Report is a survey of the state of global happiness released by the United Nations.
• This report is for the year 2019 and ranks countries by their happiness levels using the following factors:
o Overall Rank: The country’s rank based on the happiness score
o Country or Region
o Score: happiness score
o GDP per Capita
o Health Life Expectancy
o Freedom to make life choices
o Perception of Corruption
o Generosity
Source: [Link]
FINAL DASHBOARD
ASK YOUR PERSONAL AI INSTRUCTOR
• My name is Ryan, I am an absolute beginner in Power BI, and I am taking an online course. I
want you to be my personal AI instructor or teacher assistant so I will start asking you questions.
• The data that I will analyze is from the world happiness report; what is it?
LET’S CONNECT OUR
FIRST DATA
SOURCE!
POWER BI DATA CONNECTORS
• Power BI supports various data connectors, allowing users to access data from different sources:
o File-based connectors: Excel, CSV, XML, JSON, Text
o Database connectors: SQL Server, Azure SQL Database, MySQL, Oracle, IBM Db2
o Cloud and online services connectors: SharePoint Online List, Dynamics 365, Salesforce, Google Analytics
LET’S CONNECT OUR FIRST DATASET
• Load the Project1_dataset.csv and click on “Transform Data”
LET’S EXPLORE THE QUERY EDITOR
• Power BI Desktop comes with a powerful Power Query Editor, which is used to shape and transform data.
• Please note that:
o Three steps: 1. Source, 2. Promoted Headers, and 3. Changed Type have been automatically applied.
o Note that at source, all columns had the text “ABC” datatype, and Power BI automatically changed them.
o Let’s explore the M code!
LET’S CHANGE THE NAME OF THE TABLE
LET’S VIEW THE AVAILABLE DATATYPES
• Decimal number: handle numbers with
fractional values & whole numbers.
• Whole number represents a 64-bit (eight-byte)
integer with no digits to the right of the decimal
place.
• Percentage and Data/Time: Self-Explanatory!
• Resource:
[Link]
/desktop-data-types
LET’S SORT THE TABLE BASED ON THE RANK
• Note that a new applied step titled “Sorted Rows” that has been added.
• Click on “Close and Apply” in the top left corner!
NOW YOU CAN SEE THE DATA ON THE RIGHT-HAND SIDE!
• Note that some number fields have a Sigma ∑ symbol next to them.
• They are aggregates, meaning they will be summed or averaged, for example.
YOU CAN ALSO VIEW THE DATA IN THE MODEL VIEW!
ASK YOUR PERSONAL AI INSTRUCTOR
• I have a CSV file titled “Project1_dataset.csv” containing the world happiness report data I
mentioned earlier; how can I connect to it in Power BI?
• I connected the data and opened the power query editor, but now I see 3 steps applied on the
right-hand side; why is that?
PRACTICE OPPORTUNITY
In this practice opportunity, we want to connect to the 2015 World Happiness Report instead of 2019.
1. Using Power BI, create new queries to connect to the “Project1_dataset_PracticeOpportunity” dataset. You
should find the dataset available in the course package.
2. Rename the table “Happiness_Report_Data_2015”
3. Confirm that headers have been successfully promoted
4. Ensure that the data types are correct
Note:
In the 2015 dataset, columns are slightly different compared to the 2019 dataset:
• Country – Name of the country
• Region – The region the country belongs to
• Standard Error – The standard error of the happiness score
• Economy (GDP per Capita) – The extent to which GDP contributes to the calculation of the happiness score
• Family - The extent to which Family contributes to the calculation of the happiness score
• Health (Life Expectancy) - The extent to which health contributes to the calculation of the happiness score
• Freedom - The extent to which Freedom contributes to the calculation of the happiness score
• Trust (Government Corruption) - The extent to which perception of corruption contributes to the calculation of the happiness score
• Generosity – The extent to which Generosity contributes to the calculation of the happiness score
• Dystopia Residual – Dystopia is an imaginary country with the world’s least-happy people. The purpose of establishing Dystopia is to have a benchmark against
which all countries can be favorably compared (no country performs more poorly than Dystopia)
PRACTICE OPPORTUNITY SOLUTION
POWER QUERY
EDITOR:
TRANSFORM VS.
ADD COLUMN
TRANSFORM VS. ADD COLUMN
• In the Power Query Editor of Power BI, both "Add Column" and "Transform" options are used for manipulating
and modifying data. However, they serve different purposes and have different effects on the data:
o Transform: used to modify existing data. It includes various operations like renaming columns, changing
data types, removing columns, splitting or merging columns, and applying multiple transformations to the
data, such as replacing values, filling missing values, or aggregating data.
o Add Column: this option allows you to create new columns in the dataset based on existing columns.
When you add a column, you're not modifying the original data; instead, you're creating a new column that is
derived from the existing data.
ADD COLUMN EXAMPLE: CONVERTING TO UPPER CASE
• Let’s add a new column that converts the first letter to the upper case of the “country or region” column.
• Note that a new column has been created; you can also rename this column!
LET’S REMOVE THIS COLUMN
• Note that the steps remain, which is inefficient. Let’s remove the steps.
TRANSFORM COLUMN EXAMPLE: LET’S CONVERT THE
COUNTRY OR REGION COLUMN TO UPPER CASE
• Let’s capitalize each word in the “country or region” column using the transform menu.
• Note that the “country or region” column itself has been changed, and no columns have been created.
ASK YOUR PERSONAL AI INSTRUCTOR
• What is the difference between “transform” and “add column” in Power BI?
• The CSV file titled “Project1_dataset.csv” has the following columns:
• Overall Rank
• Country or Region
• Score
• GDP per Capita
• Health Life Expectancy
• Freedom to make life choices
• Perception of Corruption
• Generosity
• I want to capitalize each word in the Country or region column
PRACTICE OPPORTUNITY
1. Using the World Happiness Report 2015 dataset, capitalize each word in the “Region” Column
2. Remove the previous step.
3. Convert every character into upper case instead.
4. Replace “and” with “&” values in the “Region” column.
PRACTICE OPPORTUNITY SOLUTION
POWER QUERY
EDITOR: DEALING
WITH NUMERICAL
VALUES
LET’S OBTAIN THE AVERAGE GDP PER CAPITA
• If you try calculating the average GDP using the Add column menu, Power BI won’t allow it!
• This makes sense because the operation returns one single value.
• Therefore, this operation is only available in the transform menu only
LET’S COUNT THE NUMBER OF UNIQUE ITEMS IN A GIVEN COLUMN
• We can count the number of unique items in a given column.
• Note that the process is an exploratory data analysis only and doesn’t create a new column.
• Also note that for text columns, you can only “count values” and “count distinct values”.
LET’S ROUND THE “PERCEPTION OF CORRUPTION” COLUMN
TO 2 DECIMAL PLACES
• Click on Transform data to open the query editor and edit existing datasets
ASK YOUR PERSONAL AI INSTRUCTOR
• I have a CSV file titled “Project1_dataset.csv” that has the following columns:
• Overall Rank
• Country or Region
• Score
• GDP per Capita
• Health Life Expectancy
• Freedom to make life choices
• Perception of Corruption
• Generosity
• How can we round the perception of corruption column to two decimal places?
• When I try to obtain the average for the GDP per Capita column, the option is
not available in the add column menu. Why is that?
PRACTICE OPPORTUNITY
1. In the 2015 world happiness report, calculate the maximum, minimum, and average Family Score.
2. Count the Number of unique Items in the “Region” Column.
3. Round the Dystopia column to 3 decimal places.
PRACTICE OPPORTUNITY SOLUTION
PRACTICE OPPORTUNITY SOLUTION