Scribd
Upload
13 views3 pages

Session 4 - Activity

The document outlines a business problem for a restaurant chain that involves analyzing customer reviews and ratings to improve satisfaction and service quality. It details the datasets required, including restaurant and country information, and specifies tasks for data cleaning and transformation using Power Query and DAX in Power BI. Key tasks include merging datasets, calculating average ratings, handling null values, and creating new columns based on specific criteria before loading the data into Power BI for further analysis.

Uploaded by

adityaappleid588
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
13 views3 pages

Session 4 - Activity

The document outlines a business problem for a restaurant chain that involves analyzing customer reviews and ratings to improve satisfaction and service quality. It details the datasets required, including restaurant and country information, and specifies tasks for data cleaning and transformation using Power Query and DAX in Power BI. Key tasks include merging datasets, calculating average ratings, handling null values, and creating new columns based on specific criteria before loading the data into Power BI for further analysis.

Uploaded by

adityaappleid588
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

ACTIVITY

Business problem:

You are an analyst for a chain of restaurants. The company has collected data on customer reviews
and ratings from various branches worldwide. Your task is to clean and transform the dataset to
uncover insights efficiently that can help improve customer satisfaction, menu preferences, and
overall service quality.

We will use Power Query for cleaning and transformation and after that we will create calculated
tables, measures and columns using DAX.

DATASETS:

Restaurant dataset

This dataset consists of reviews of various restaurants, including details like restaurant ID,
restaurant name, customer ratings, review text, and more.
● Restaurant ID: Unique Identifier
● Restaurant Name: Name of the restaurant
● Country Code:Country in which restaurant is located
● City: Name of the City
● Cuisines: Name of the cuisines available in the restaurant
● Average Cost for two: Cost for two people in different currencies
● Currency: Currency of the country
● Price range: range of price of food
● Aggregate Rating: Average rating out of 5
● Rating color: depending upon the average rating color
● Votes: Number of ratings casted by people

Country details:

● Country Code: Code of the Country in which restaurant is located


● Country name: Name of the Country in which restaurant is located.

TASKS TO BE PERFORMED:

Power Query Editor


● In the Country Details dataset, convert the first row to header
● Convert the Country Details dataset into this format by unpivoting

1
Country Code Country Name

1 India

14 Australia

… …

● If any of the two datasets is adding extra columns at the end of the dataset, remove them
● Merge the Country Details dataset (in the new format) to the Restaurant Details dataset in a
new query.
● For each country, find average Aggregate Rating and sum of votes
● In the Cuisine column, wherever the value is null, replace it with “All”.
● Many restaurants have more than one cuisine. Create new columns for each cuisine: Cuisine
1, Cuisine 2 and so on for each restaurant. (Keep the original Cuisine column also by
duplicating it). This is shown below:

● Create a new column with values in this format: Rs. 100, P 100, R$ 100, $100. First the
currency symbol and then the value of Average Cost of two.
● Based on the Rating column, create a new column with the following criteria:
○ If Rating < 3, then ‘Poor’
○ If Rating >= 3 and < 4, then ‘Good’
○ If Rating >=4 and <=5 then ‘Very Good’
○ Else ‘NA’
● Find out which restaurant in India had the highest product of Votes and Rating

After the above cleaning and transformations are done, Close and Load the data into Power BI.

● After loading the dataset, go to the Model View in Power BI Desktop and ensure a
relationship (on Country Code column) between the 2 tables (Country Details and
Restaurant) is active.

INSTRUCTIONS:

2
● Download the dataset from the provided link.
● Import both the datasets into Excel using Power Query ("Data" tab > "Get & Transform
Data" section).
● Apply the necessary transformations, such as filtering out irrelevant data and extracting key
information from textual data.
● Aggregate the cleaned data to perform the summarization tasks mentioned above.
● Load the transformed data into PowerBI to use DAX or to create reports or visualizations.

You might also like