Quiz 3: Data Cleaning and Transformation

A report contains the same Order ID, product, and amount twice after two overlapping files were combined. What should the analyst do in Power Query? A. Select the identifying columns and choose Remove Duplicates B. Use Fill Down on the amount C. Append the table again D. Change Order ID to decimal

A. Select the identifying columns and choose Remove Duplicates

A store records gender as “Female,” “F,” and “Woman.” Which tool can map these variations into one approved category? A. Replace Values or a Conditional Column B. Remove Columns only C. Append Queries D. Change Type Using Locale

A. Replace Values or a Conditional Column

A delivery record has a blank status, and the organization confirms that every blank means the case is awaiting review. What is the most appropriate action? A. Replace the blank with “Pending Verification” B. Replace it with zero C. Delete the entire dataset D. Convert the column to a date

A. Replace the blank with “Pending Verification”

Dates stored as text appear in alphabetical rather than chronological order. What should the analyst do? A. Convert the column to the Date data type B. Merge the dates with another table C. Replace every date with today D. Convert the dates to true or false

A. Convert the column to the Date data type

January Sales has 500 rows and February Sales has 450 rows with matching column names and compatible data types. Which operation creates one 950-row table? A. Merge Queries B. Append Queries C. Remove Errors D. Fill Up

B. Append Queries

A quantity column contains -5 even though product quantities cannot be negative. How should this value be classified? A. A duplicate value B. An invalid or incorrect value C. A missing value D. A sorting priority

B. An invalid or incorrect value

A manager wants a view containing only Central Visayas orders above ₱5,000 from September 2026. Which operation is required? A. Filtering with multiple conditions B. Sorting only C. Appending monthly tables D. Merging customer details

A. Filtering with multiple conditions

A sales report treats “Metro Manila,” “NCR,” and “National Capital Region” as three regions. What cleaning action should the analyst perform? A. Remove all location records B. Standardize the category values C. Convert the region column to a number D. Sort the regions alphabetically

B. Standardize the category values

A dataset lacks a customer ID for several rows, and the ID is essential for linking transactions to customers. Which treatment is most defensible? A. Remove or isolate those records because a critical value is missing B. Fill every ID with zero C. Copy the first customer ID down all rows D. Sort the blank values to the bottom and ignore them

A. Remove or isolate those records because a critical value is missing

Two monthly tables describe the same records, but one uses “Order Amount” and the other uses “Sales Value.” What should be done before appending? A. Standardize the corresponding column names B. Convert all data to images C. Delete one monthly table D. Merge using customer name

A. Standardize the corresponding column names

Before removing duplicates, why should an analyst select the columns that identify a unique record? A. To ensure only records representing the same entity or transaction are removed B. To convert all columns into keys automatically C. To sort every column in descending order D. To replace null values with blanks

A. To ensure only records representing the same entity or transaction are removed

An analyst wants to keep all records but arrange them first by region and then by order amount from highest to lowest. What should be used? A. Sorting with multiple-column priority B. Filtering to remove low amounts C. Removing duplicates D. Replacing values

A. Sorting with multiple-column priority

A product price column will be used for precise currency calculations. Which data type from the module is most suitable? A. True or false B. Fixed decimal number C. Text D. Date and time

B. Fixed decimal number

An Orders table contains Customer ID and Amount, while a Customers table contains Customer ID, Name, and City. Which operation adds Name and City to each order? A. Append using corresponding columns B. Merge using Customer ID as the matching field C. Sort by Customer ID D. Remove duplicate customers from Orders

B. Merge using Customer ID as the matching field

Student IDs such as 00127 must retain their leading zeros and will not be used in arithmetic. Which data type is appropriate? A. Whole number B. Decimal number C. Text D. Date

C. Text

A customer order has no amount because the value was not captured, but an analyst replaces it with zero without verification. Why is this risky? A. Zero may incorrectly imply that the order had no monetary value B. Power Query cannot store zero C. Zero always creates a duplicate D. The record will automatically become text

A. Zero may incorrectly imply that the order had no monetary value

A cleaned dataset still produces broken relationships and misleading charts. Which principle from the module should guide the next review? A. Analysis quality depends heavily on source-data quality B. Sorting always repairs relationships C. All missing numerical values should become zero D. Appending always removes errors

A. Analysis quality depends heavily on source-data quality

A city field contains “ Cebu City ” with unwanted spaces. Which Power Query operation is most suitable? A. Trim and Clean B. Append Queries C. Change Type Using Locale D. Fill Up

A. Trim and Clean

The value 05/06/2026 should mean 5 June, but Power Query interprets it as May 6. Which feature should correct the conversion? A. Remove Duplicates B. Change Type Using Locale C. Append Queries D. Fill Down

B. Change Type Using Locale

A company wants all rows from Orders and matching customer details when available, while retaining orders with no customer match. Which join type is appropriate? A. Left outer join with Orders as the left table B. Inner join C. Anti join D. Right outer join with Customers as the right table

A. Left outer join with Orders as the left table