Scribd
Upload
5 views2 pages

Midterm

The document outlines instructions for a midterm project involving data management using Excel and Power Query. Students are required to create a specific folder, download files, and perform various data manipulation tasks including VLOOKUP, IFNA, and creating pivot tables. Additionally, students must calculate net amounts, correct date-time formats, and establish connections between different datasets using Power Query.

Uploaded by

31241022519
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
5 views2 pages

Midterm

The document outlines instructions for a midterm project involving data management using Excel and Power Query. Students are required to create a specific folder, download files, and perform various data manipulation tasks including VLOOKUP, IFNA, and creating pivot tables. Additionally, students must calculate net amounts, correct date-time formats, and establish connections between different datasets using Power Query.

Uploaded by

31241022519
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

Midterm

Note:
 Go to C drive in your computer, create a folder and name it as “Midterm_BA”.
Download and store all the files in this folder (the link should be like “C:\
Midterm_BA”). For Macbook users, just put it in Download folder.
 Create 01 excel file and name it No_StudentID_YourName (e.g.
01_31241099955_NguyenVanAnh).
 Use Power Query to connect data from all the files to this file.
 You are required to submit everything within this file. The sheet that contains your
answer should be named clearly (e.g. Question01, photo below).

1. depositList - (2 point)
- Open "depositList", use VLOOKUP to get the Channel name from "VNPay_D".
- Use IFNA to turn the "N/A" into "Other".
- Turn the data range into the data table and name it after yourName_studentID.
- Insert a Pivot table and show the sum of “SourceAmount”. Change the number format
into Currency. Draw a chart of your liking using that pivot table.

2. NL, power query – (1 points)


- For "Transaction type" column, filter to show only "Receive payment". Right click on
column "Code invoice / Product Name" and choose "Remove Duplicates".

3. NL – Net mount and Time (2 point)


- Knowing whether it is a "Sender fee" or a "Receiver fee," the transaction costs are
always borne by the business owner. Among these, the "Withdraw" transaction refers to
withdrawing money from the business account, while the "Receive payment" transaction
refers to receiving payments from customers. Use power query to create a new column
named "Net amount" to calculate the net amount after all the fees. Turn that value into
Absolute value.
- Use Power Query to correct the invalid date and time in the column “Time created” and
“Tim success” to proper date-time data and rename the column to “Time success”.
Convert the time to GMT+8, knowing that the data in the file is in GMT+7.
Note: Add a **Custom Column** and enter the following formula to add 1 hour: [Name of
the accurate DateTime column] + #duration(0, 1, 0, 0)

4. PA (1 point)
- Connect and “PA_D” to “depositList” to find which orders are used with PA using
Power Query (note: create a column use Merge query).
- Find a solution to find the correct connection between “PA_W” and “WithdrawalList”.

You might also like