BYU Student Author: @Kyle_Nilsen
Reviewers: @Marta_Ellsworth, @Jacob_Dutton
Estimated Time to Solve: 15 Minutes
This challenge was written in partnership with a business professional. The data, scenario, or both were inspired by real-world business scenarios.
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
You are a new associate at a local accounting firm. One of your clients has requested assistance with a project aimed at analyzing and evaluating transactions processed through one of their third-party software, Stripe. The transaction list is given to you in an Excel file, but it contains both logic and syntax errors. However, the client wants to ensure the original data is preserved and not modified in any way. To work around this, you will need to use PowerQuery and PivotTables. PowerQuery is an especially useful tool as it records and displays each step taken to modify the original data source so that your work can be reproduced. Or, if the original data is modified, the query will automatically update to reflect the changes. Your task is ultimately to present a clean, professional, summarized view of these transactions to your client.
Instructions
- Download the Challenge215_Powering_Up_PowerQuery Excel file included at the bottom of the challenge.
- Create a new Excel workbook in order to use power query. Import the Challenge215_Powering_Up_PowerQuery file and then open the query editor.
- This step focuses on cleaning and preparing the data within the query editor.
a. There are several errors within the data that will prevent aggregation functions being used later on. Delete all rows in the gross, fee, and net columns that have errors.
b. The data has some blank rows resulting from different export times. Remove all blank rows.
c. There is an error in the fees column: every record which falls under the “dispute” category has its “fee” value set to 2, which is actually an internal ID. It is meant to be a static value, 17. Change every occurrence of the value 2 in the “fee” column to 17.
d. Group the data by customer (“automatic_payout_id”) and “reporting_category” to find the total number of transactions and the totals of the “gross”, “fee”, and “net” columns.
e. Close and load the query into your Excel workbook. - Now you have a query presenting some good, clean data. However, it is not easy to read for quick analysis with totals and subtotals per customer. Use the query you just created to create two PivotTables.
a. The first PivotTable should consist of the totals for each “reporting_category” for “gross,” “fee,” and “net.” Also include a count of total records for each category.
b. The second PivotTable will be similar to the first, but it should break out the totals across each customer (“automatic_payout_id”). In other words, there should be subtotals for each customer, and then beneath that subtotal, break out the subtotals for each “reporting_category.”
Data Files
Suggestions and Hints
- To import an Excel file into another workbook, go to the Data tab, click on Get Data
From File From Excel Workbook, then select the Challenge215_Powering_Up_PowerQuery.xslx data file. A preview of the data will display, and you can either select “Transform” before loading it, or you can load it first and then click on the produced table, followed by the green “Query” tab on the right of the toolbar, then “Edit.” - For the two “delete [type] rows” steps, highlight the relevant columns, then use the dropdown on the “Remove Rows” item on the home tab to select the relevant type of removal.
- You can replace incorrect values by going to the Transform tab and using “Replace Values” after highlighting the relevant column.
- Use “Group By” on the transform tab to do grouping. For this challenge, you will need to use advanced grouping, by both automatic_payout_id and reporting_category. Then you will need to create four different aggregations, for a count of total rows (NOT distinct), and a sum of each gross, fee, and net.
- The first PivotTable will use rows of reporting_categories and values of the count and sums you calculated in your query. The second PivotTable will differ only by including another rows attribute for automatic_payout_id and removing the count of total records from the values.
Solution
Note: In order to view the steps and solution for my query, you will need to open the query editor as described above, then on the home tab select “Advanced Editor,” then change “–INPUT FILE PATH HERE–" with the file path to where you saved the Challenge215_Powering_Up_PowerQuery.xslx file.
Challenge215_Solution.xlsx
Solution Video: Challenge 215|EXCEL – Powering Up PowerQuery
