Module Code
\nDE4004FP
\nDuration
\n5 hours
\nCopyright \u00a9 ITE 2020
\nModule
\nData Processing
\nHigher Nitec in Data Engineering
\nAssignment 1
\n1
\nTitle :
\nApply data transformation and merging methods to a given dataset.
\nTools, Equipment and Materials:
\n\u2022 Personal Computer with Internet access
\n\u2022 Data Processing Software
\nInstructions & Guidelines:
\n1. Submissions should be made via the myConnexion by the stated
\ndeadline.
\n2. No marks will be awarded, if the work is copied or you have
\nallowed others to copy your work.
\nSubmission:
\n3. Submission should include the following items:
\n\u2022 Processed Excel dataset_Your name
\n\u2022 Microsoft Power BI file_Your name
\n\u2022 Data Profile.jpg
\n\u2022 Relationship.jpg
\nModule Code
\nDE4004FP
\nDuration
\n5 hours
\nCopyright \u00a9 ITE 2020
\nModule
\nData Processing
\nHigher Nitec in Data Engineering
\nAssignment 1
\n2
\nTask Requirements
\nDataset:
\nIt contains customer treadmill purchase information.
\nThe meaning of the data columns is given in the Excel file.
\nList of any Excel functions can be used in this assignment:
\nAND
\nIF or CountIF
\nSort
\nPivotTable
\nPivot Table\u2019s Calculated field
\nVlookup
\nProvide your answers in the given Excel spreadsheet.
\nPart (A): Data transformation
\nUse Excel functions on the dataset to answer the following
\nquestions:
\n(NB : If Excel function does not work, ensure the data type columns
\nare correct)
\n1. Display top 10 customers according to income and marital status
\nshowing \u201cPartnered\u201d status.
\n2. Identify customers who have fitness level 4 and above, with a
\nusage level of 4 and above. Tag this group of people as
\n\u201cReward\u201d, and, \u201cNo Reward\u201d who does not meet these
\nrequirements.
\nSample Result:
\n3. Count the number of customers whose age are less than 21
\nyears old?. ( Hint: Same method as question 2.)
\nModule Code
\nDE4004FP
\nDuration
\n5 hours
\nCopyright \u00a9 ITE 2020
\nModule
\nData Processing
\nHigher Nitec in Data Engineering
\nAssignment 1
\n3
\n4. Use the Figure 1 table, add on this new information in the
\ndataset. The new colum will show when the product is created.
\nFigure 1:
\nSample Result:
\n5. Use Figure 2 table, Replace the income into astericks to hide this
\nsensitive data
\nFigure 2
\nSample Result:
\nModule Code
\nDE4004FP
\nDuration
\n5 hours
\nCopyright \u00a9 ITE 2020
\nModule
\nData Processing
\nHigher Nitec in Data Engineering
\nAssignment 1
\n4
\n6. Use Pivot Table function, create a report which display the total
\nsum usage of the product by gender, marital status.
\n7. Use Pivot Table function, create a report which display on the
\ncount of customers by Reward or No Reward over a list of
\nproducts.
\n8. Use Pivot Table function, create a report which display the list of
\ncustomers who prefer \u201cNo paperless billing\u201d.
\n9. Use Pivot Table\u2019s Calculated field function, create a report which
\ndisplay gender, product and miles per usage. ( Formula miles
\ndivided by usage)
\n10. Use the Pivot Table\u2019s Calculated field function, create a report
\nwhich display a list of customer, gender is Male, Usage is 4 and
\nabove and miles per usage.
\nPart (II): Tables Merging using Microsoft Power Bi
\n1. Load the Part 1 completed processed Excel file and
\ncountryExcel files into Microsoft BI.
\n2. On countryExcel data file, ensure product column is in the
\ncorrect data format.
\n3. Use product column in countryExcel file, create a data profile
\nand screenshot the result, name it as Data Profile.jpeg
\nfile, copy and paste it on the Part 1 Excel file.
\n4. In Microsoft Power BI, create new relationship tables merging
\nfor these two datasets. Select the correct cardinality
\nrelationship and cross filter direction. Screenshot the result,
\nname it as relationship.jpeg file, copy and paste it on the
\nPart 1 Excel file.
\nModule Code
\nDE4004FP
\nDuration
\n5 hours
\nCopyright \u00a9 ITE 2020
\nModule
\nData Processing
\nHigher Nitec in Data Engineering
\nAssignment 1
\n5
\nPart (III): Flowchart
\n1. Refer to the \u201cFlowchart\u201d tab in the Processed Excel file.
\n2. Complete the missing entry in the flowchart.
\n3. Save and submit these files :
\n\u2022 Processed Excel dataset_Your name
\n\u2022 Microsoft Power BI file_Your name
\n\u2022 Data Profile.jpg
\n\u2022 Relationship.jpg
\n– END –<\/p>\n","protected":false},"excerpt":{"rendered":"
Module Code DE4004FP Duration 5 hours Copyright \u00a9 ITE 2020 Module Data Processing Higher Nitec in Data Engineering Assignment 1 1 Title : Apply data\u2026<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8816],"tags":[],"class_list":["post-115523","post","type-post","status-publish","format-standard","hentry","category-thesis-writing-help-online"],"_links":{"self":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/115523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/comments?post=115523"}],"version-history":[{"count":0,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/posts\/115523\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/media?parent=115523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/categories?post=115523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.studyproessays.com\/help\/wp-json\/wp\/v2\/tags?post=115523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}