Posted: December 7th, 2022

DE4004FP

Module Code
DE4004FP
Duration
5 hours
Copyright © ITE 2020
Module
Data Processing
Higher Nitec in Data Engineering
Assignment 1
1
Title :
Apply data transformation and merging methods to a given dataset.
Tools, Equipment and Materials:
• Personal Computer with Internet access
• Data Processing Software
Instructions & Guidelines:
1. Submissions should be made via the myConnexion by the stated
deadline.
2. No marks will be awarded, if the work is copied or you have
allowed others to copy your work.
Submission:
3. Submission should include the following items:
• Processed Excel dataset_Your name
• Microsoft Power BI file_Your name
• Data Profile.jpg
• Relationship.jpg
Module Code
DE4004FP
Duration
5 hours
Copyright © ITE 2020
Module
Data Processing
Higher Nitec in Data Engineering
Assignment 1
2
Task Requirements
Dataset:
It contains customer treadmill purchase information.
The meaning of the data columns is given in the Excel file.
List of any Excel functions can be used in this assignment:
AND
IF or CountIF
Sort
PivotTable
Pivot Table’s Calculated field
Vlookup
Provide your answers in the given Excel spreadsheet.
Part (A): Data transformation
Use Excel functions on the dataset to answer the following
questions:
(NB : If Excel function does not work, ensure the data type columns
are correct)
1. Display top 10 customers according to income and marital status
showing “Partnered” status.
2. Identify customers who have fitness level 4 and above, with a
usage level of 4 and above. Tag this group of people as
“Reward”, and, “No Reward” who does not meet these
requirements.
Sample Result:
3. Count the number of customers whose age are less than 21
years old?. ( Hint: Same method as question 2.)
Module Code
DE4004FP
Duration
5 hours
Copyright © ITE 2020
Module
Data Processing
Higher Nitec in Data Engineering
Assignment 1
3
4. Use the Figure 1 table, add on this new information in the
dataset. The new colum will show when the product is created.
Figure 1:
Sample Result:
5. Use Figure 2 table, Replace the income into astericks to hide this
sensitive data
Figure 2
Sample Result:
Module Code
DE4004FP
Duration
5 hours
Copyright © ITE 2020
Module
Data Processing
Higher Nitec in Data Engineering
Assignment 1
4
6. Use Pivot Table function, create a report which display the total
sum usage of the product by gender, marital status.
7. Use Pivot Table function, create a report which display on the
count of customers by Reward or No Reward over a list of
products.
8. Use Pivot Table function, create a report which display the list of
customers who prefer “No paperless billing”.
9. Use Pivot Table’s Calculated field function, create a report which
display gender, product and miles per usage. ( Formula miles
divided by usage)
10. Use the Pivot Table’s Calculated field function, create a report
which display a list of customer, gender is Male, Usage is 4 and
above and miles per usage.
Part (II): Tables Merging using Microsoft Power Bi
1. Load the Part 1 completed processed Excel file and
countryExcel files into Microsoft BI.
2. On countryExcel data file, ensure product column is in the
correct data format.
3. Use product column in countryExcel file, create a data profile
and screenshot the result, name it as Data Profile.jpeg
file, copy and paste it on the Part 1 Excel file.
4. In Microsoft Power BI, create new relationship tables merging
for these two datasets. Select the correct cardinality
relationship and cross filter direction. Screenshot the result,
name it as relationship.jpeg file, copy and paste it on the
Part 1 Excel file.
Module Code
DE4004FP
Duration
5 hours
Copyright © ITE 2020
Module
Data Processing
Higher Nitec in Data Engineering
Assignment 1
5
Part (III): Flowchart
1. Refer to the “Flowchart” tab in the Processed Excel file.
2. Complete the missing entry in the flowchart.
3. Save and submit these files :
• Processed Excel dataset_Your name
• Microsoft Power BI file_Your name
• Data Profile.jpg
• Relationship.jpg
– END –

Order | Check Discount