5  Data Preparation and Cleaning in Excel

Learning Objectives

After completing this chapter, you will be able to:

1. Explain why accounting data must be prepared before it is analyzed, and document the preparation so that a reviewer can repeat it.
2. Use text and logical functions to extract, standardize, classify, and validate fields, such as the product type encoded in an item code or the payment terms of an invoice.
3. Use date functions to assign transactions to fiscal periods and to measure the time between business events.
4. Combine data from related tables with XLOOKUP and with INDEX and MATCH, read the VLOOKUP formulas of older workbooks, and recognize when a one-to-many relationship calls for a different approach.
5. Build a repeatable preparation workflow in Power Query that merges tables, adds calculated columns, and can be refreshed when the source data changes.

Chapter slides View slides Download PowerPoint

Opening Scenario

The analysis workbook you built in Chapter 4 ended with a clue and an open question. The Furniture Seasonal Promotion carries more invoice lines and more discount dollars than any other promotion, and its effective dates, September and October 2026, straddle the two quarters whose margins the controller wants explained. When you tell her, she asks the obvious next question: how many of those discounted lines fell in the fourth quarter, and how much of the Furniture revenue did they account for? You cannot answer yet. The invoice lines record what was sold, at what price, and under which promotion, but not when the invoice was issued, which product group the item belongs to, or which customer bought it. That information lives in other Tables: the invoices, the items, the customers, and the sales orders. Before the Furniture analysis of Chapters 6 and 7 can begin, you have to bring it together, check that it came together correctly, and make the result easy to refresh. This chapter teaches you how.

5.1 Why Data Preparation Comes First

Data preparation is the work of transforming data from the form in which it was recorded into a form that suits a particular analysis. A review of the field describes it as the process of discovering, cleaning, integrating, and restructuring data, and it names the activities involved: profiling the data, matching records across sources, mapping one structure onto another, transforming formats, and repairing values that are wrong (Fernandes et al., 2023). In the analytics workflow you met in Chapter 1, it is the third stage, after the question has been defined and the data has been obtained, and before any analysis begins.

For accountants, preparation is rarely optional. Transaction systems store data in the form that suits recording it, spread across related tables, as you saw in Chapter 3. A row-level analysis in Excel needs a different form: one table in which every row carries all the attributes the question depends on. Auditors face the same problem with data they receive from clients. Lee et al. (2021) note that client data often arrives incomplete and in an unusable format, so that considerable time must be spent cleansing, transforming, standardizing, and validating it before it can be analyzed. The same concern runs through accounting education. In a review of published data analytics teaching cases, Raschke and Charron (2021) found that most cases focus on analysis and communication, and that few cover the steps that come before analysis, such as extraction and data preparation.

Preparation is also where many analytical errors begin. A lookup that silently fails, a date assigned to the wrong quarter, or a join that duplicates rows can change every number that follows, and none of these errors announces itself. That is why this chapter pairs every preparation technique with a check: a count of the rows that failed to match, a total that must agree with its source, or a test that a field has the form it should. The quality dimensions you studied in Chapter 2, accuracy, completeness, consistency, and timeliness, are the standard the checks apply. And because preparation involves many small decisions, it has to be documented, as you learned in Chapter 4, so that someone else can repeat it and reach the same result.

Figure 5.1 shows the destination of this chapter. By the end of Tutorial 5.3, your workbook will contain a single table, InvoiceLines, in which every invoice line carries its invoice date and fiscal period, its item’s group and product type, and its customer’s segment and region. Chapters 6 and 7 analyze that table.

Figure 5.1: From Related Tables to One Analytical Table.

5.2 Text Functions

Text fields carry more information than their appearance suggests. An item code, an invoice number, or an account name often encodes attributes, such as a category, a year, or a sequence, that are not stored anywhere else. Text functions let you extract those attributes, standardize text so that values that mean the same thing also look the same, and test that each value has the form it should. Table 5.1 summarizes the functions this chapter uses.

Table 5.1: Text functions used for data preparation

Function	What it returns	Example with a Charles River value
LEFT(text, n)	The first n characters	LEFT("FUR-DSK-0075",3) returns FUR
MID(text, start, n)	n characters starting at a position	MID("FUR-DSK-0075",5,3) returns DSK
RIGHT(text, n)	The last n characters	RIGHT("SI-2025-007947",6) returns 007947
LEN(text)	The number of characters	LEN("FUR-DSK-0075") returns 12
FIND(find, text)	The position of one text inside another	FIND("-","FUR-DSK-0075") returns 4
TRIM(text)	The text without leading, trailing, or repeated spaces	Removes stray spaces from an imported name
CLEAN(text)	The text without the basic non-printing control characters	Removes line breaks copied in from another system
SUBSTITUTE(text, old, new)	The text with one string replaced by another	Replaces “Street” with “St” to standardize addresses
UPPER, LOWER, PROPER	The text in capitals, lower case, or with each word capitalized	UPPER("Smith Ltd") returns SMITH LTD
EXACT(text1, text2)	TRUE if the two texts are identical, including their case	EXACT("LLC","Llc") returns FALSE
VALUE(text)	The number that a text value represents	Converts "007947" to 7947

5.2.1 Extracting Parts of a Text Value

Charles River’s item codes follow a pattern. Every item the company sells has a code of three parts separated by hyphens: a three-letter group code, a three-letter product type code, and a four-digit number. FUR-DSK-0075 is a desk in the Furniture group, and FUR-SDB-0002 is a sideboard. The Item Table has an ItemGroup column, but no column for the product type, so the only way to analyze sales by product type, such as desks against sideboards, is to extract it from the code. Because the parts have a fixed length, the formula =MID([@ItemCode],5,3) returns the product type for every item: it takes three characters starting at the fifth. When the parts of a code vary in length, you can locate the separators with FIND and compute the positions instead, and Microsoft 365 adds TEXTBEFORE and TEXTAFTER, which return the text before or after a separator directly.

Invoice numbers encode information too. SI-2025-007947 contains a document prefix, a year, and a sequence number. The year in the number is assigned when the invoice is numbered, and in a well-controlled system it should agree with the year of the invoice date. Extracting it with =MID([@InvoiceNumber],4,4) and comparing it with the invoice date is a simple validity test, which you will apply in the exercises.

5.2.2 Standardizing Text

Values that mean the same thing should look the same, or lookups, filters, and counts will treat them as different. TRIM removes spaces at the start and end of a value and reduces repeated spaces to one. CLEAN removes the basic non-printing control characters, such as line breaks, that are often copied in from other systems, although it leaves some invisible characters in place, as the Watch out below explains. SUBSTITUTE replaces one string with another, which is useful for standardizing abbreviations. UPPER, LOWER, and PROPER change the case of the letters.

Standardizing should be deliberate, because it can damage values that were already correct. PROPER capitalizes the first letter of every word and lowers the rest, so it turns a supplier named Donaldson LLC into Donaldson Llc and the connecting word in Watson, Mitchell and Chen into And. It also changes item names such as a sideboard “48 in” wide into “48 In”. When a field is already consistent, the right preparation step is to leave it alone and record that you checked it.

5.2.3 Validating Text

Before you clean a field, test whether it needs cleaning. This formula returns TRUE when a name has no stray spaces:

=LEN([@CustomerName])=LEN(TRIM([@CustomerName]))

A COUNTIFS on the result tells you how many values fail. Applied to the Charles River names, codes, and addresses, tests like this find nothing to remove: the text fields have no stray spaces. That is a finding worth recording, not a wasted step. A preparation workflow that cleans data it has not tested can introduce errors, and one that tests and finds nothing has evidence that the field is free of the problem it tested for, although not of every other problem. You can also test the form of a value, for example that every item code has twelve characters with hyphens in the fourth and eighth positions.

WarningWatch out

Some problems are invisible on screen. Text copied from a web page can contain a non-breaking space, character 160, which looks like an ordinary space but is not removed by TRIM. If a value that looks clean still fails to match, =SUBSTITUTE(A2,CHAR(160)," ") replaces non-breaking spaces with ordinary ones before TRIM runs. Numbers stored as text are another invisible problem: they look like numbers but do not add up, and they fail to match the same number stored as a number. VALUE converts them.

5.3 Date Functions

Accounting analysis is organized by period, so assigning each transaction to the right period is one of the most common preparation steps. Excel stores a date as a number of days, which is why dates can be subtracted and compared, and it provides functions that return the parts of a date. YEAR, MONTH, and DAY return the year, month, and day. EOMONTH(date, 0) returns the last day of the date’s month, which is useful for month-end cutoff tests, the checks that each transaction is recorded in the period in which it occurred, and EOMONTH(date, -1)+1 returns the first day.

5.3.1 Assigning Fiscal Periods

Charles River’s fiscal year is the calendar year, so the fiscal year of an invoice is =YEAR([@InvoiceDate]) and its fiscal quarter is =ROUNDUP(MONTH([@InvoiceDate])/3,0), which returns 1 for January through March and 4 for October through December. A period label such as 2026-Q4 is more useful than two separate numbers when you group, sort, and filter, and you can build one by joining text: =[@FiscalYear]&"-Q"&[@FiscalQuarter].

Many organizations use a fiscal year that does not follow the calendar. For a fiscal year that begins in April, the formula =INT(MOD(MONTH([@InvoiceDate])-4,12)/3)+1 returns quarter 1 for April through June and quarter 4 for January through March. The fiscal year itself then depends on the convention the organization uses to name it, typically the calendar year in which the fiscal year ends.

5.3.2 Measuring Time Between Events

Subtracting one date from another returns the number of days between them. The formula =[@DueDate]-[@InvoiceDate] returns the payment terms of each invoice in days, and the difference between an invoice date and its order date measures how long an order took to be billed. For whole months or years between two dates, DATEDIF returns the difference in a chosen unit, as in =DATEDIF(start, end, "m"), although Excel does not suggest it as you type.

Measuring the age of a balance requires an as-of date. It is tempting to write =TODAY()-[@InvoiceDate], but TODAY returns the current date each time the workbook recalculates, so the result changes from one day to the next and cannot be reproduced. A historical extract such as the Charles River data ends on a fixed date. Store the as-of date in a named parameter cell, as you did with the threshold in Chapter 4, and subtract from it instead.

WarningWatch out

A date that is stored as text looks like a date but behaves like text: it sorts alphabetically, it fails to match a real date, and arithmetic on it either fails or depends on Excel guessing the date it represents. Text dates are common in data exported from other systems, especially when the export uses a different date order from your computer’s settings. DATEVALUE converts a text date that Excel can recognize, and Power Query’s data type setting, which you used in Chapter 4, converts a whole column. The Charles River workbook stores its dates as real dates, but always check the data type of a date column before you rely on it.

5.4 Conditional Logic

Preparation often needs a column whose value depends on a condition: a flag that marks exceptions, a label that groups values into bands, or a fallback when a formula fails. The building block is a logical test, an expression that is either true or false, such as [@DaysToDue]>45 or [@ItemGroup]="Furniture". Excel evaluates a logical test to TRUE or FALSE, and a column of such results is already useful: you can count it with COUNTIF, filter it, and use it in conditional formatting. Table 5.2 summarizes the functions that work with logical tests.

Table 5.2: Logical functions used for data preparation

Function	What it returns	Example
IF(test, if_true, if_false)	One of two values, depending on a test	Label invoices with extended payment terms
IFS(test1, value1, ...)	The value paired with the first true test	Group payment terms into bands
AND(test1, test2, ...)	TRUE only if every test is true	Check each part of an item code
OR(test1, test2, ...)	TRUE if at least one test is true	Check that terms are one of the standard values
NOT(test)	The opposite of the test	Flag the lines that are not Furniture
IFERROR(value, if_error)	The value, or a substitute if the value is an error	Return “No match” when a lookup fails

5.4.1 IF and IFS

IF(logical_test, value_if_true, value_if_false) returns one value when the test is true and another when it is false. The formula =IF([@DaysToDue]>45,"Extended","Standard") labels each invoice by its payment terms. When there are more than two outcomes, you can place one IF inside another, but a nested formula is hard to read and easy to get wrong. IFS lists the tests in order and returns the value paired with the first one that is true:

=IFS([@DaysToDue]<=30,"Up to 30 days",[@DaysToDue]<=60,"31 to 60 days",TRUE,"Over 60 days")

The last test, TRUE, is always true, so it catches every value that the earlier tests did not. Without it, a value that meets none of the tests returns the error #N/A. The order of the tests matters too: IFS stops at the first true test, so bands must be tested in order, from the lowest up or from the highest down.

5.4.2 AND, OR, and NOT

AND returns TRUE only when all of its tests are true, OR returns TRUE when at least one of them is, and NOT reverses a result. You can use them on their own, as the item code check in Tutorial 5.1 does with AND, or inside IF to combine conditions. This formula, for example, is TRUE for an invoice whose terms are one of the four that Charles River offers:

=OR([@DaysToDue]=30,[@DaysToDue]=45,[@DaysToDue]=60,[@DaysToDue]=90)

5.4.3 IFERROR

Some formulas return an error for rows that are not wrong, such as a lookup whose key is missing, which returns #N/A, or a ratio whose denominator is zero, which returns #DIV/0!. IFERROR(value, value_if_error) returns the result of a formula, or a value you choose when the formula returns any error. It makes a column readable, but use it deliberately. IFERROR hides every kind of error, including a mistyped reference, so the substitute should be a value you can count, such as “No match”, rather than a zero or a blank that disappears into totals. XLOOKUP’s own if_not_found argument, described in the next section, is narrower, because it applies only when the key is not found.

WarningWatch out

A logical test compares values exactly. Text comparisons ignore upper and lower case, so “furniture” equals “Furniture”, but an extra space or a number stored as text makes two values that look alike unequal. When a test returns FALSE for a row that looks as if it should pass, check the value for hidden spaces with the LEN and TRIM test earlier in this chapter, and check its data type.

5.5 Guided Tutorial 5.1: Adding Text, Date, and Logical Columns

Context and objective. In this tutorial, you will prepare two of the Tables in your analysis workbook for the lookups and summaries that follow. On the Item Table, you will extract each item’s product type from its code and test that every code has the expected form. On the SalesInvoice Table, you will assign each invoice to a fiscal year, a fiscal quarter, and a period label, measure its payment terms in days, and use logical functions to group the terms and test them. Each new column is a calculated column, so it fills every row and extends to rows added by a refresh.

Prerequisites. You need the Charles River Furniture Analysis workbook from Chapter 4, with the SalesInvoice, SalesInvoiceLine, Item, and PromotionProgram Tables loaded by Power Query. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Chapter 4 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Extract the product type from the item code. The product type is the middle part of the code, three characters starting at the fifth.

1. Go to the Item worksheet. Click the first empty cell to the right of the last header, LifecycleStatus, type ProductType, and press Enter.
2. In the first data row of the new column, enter =MID([@ItemCode],5,3).
3. Check a few rows: FUR-DSK-0075 should return DSK, and FUR-SDB-0002 should return SDB.

Every item now carries a three-letter product type. Table 5.3 lists the Furniture product types and their codes; other groups have their own codes, such as PND for pendant lights and RUG for rugs.

Table 5.3: Furniture product type codes

Code	Furniture product type	Code	Furniture product type
BKC	Bookcase	DSK	Desk
BNH	Bench	NGT	Nightstand
CON	Console table	SDB	Sideboard
CTB	Coffee table	TBL	Dining table

Step 2. Test that every item code has the expected form. A code of the form GRP-TYP-9999 has twelve characters, with hyphens in the fourth and eighth positions.

1. Add another column, CodeCheck, with this formula:

   =AND(LEN([@ItemCode])=12,MID([@ItemCode],4,1)="-",MID([@ItemCode],8,1)="-")

2. On the Analysis worksheet, type Item codes failing the check in A40 and enter =COUNTIF(Item[CodeCheck],FALSE) in B40.

Every item Charles River sells passes the check, so B40 shows zero: every code has its hyphens in the expected places, so MID takes the product type from the same position in every code. The check cannot show that the three letters it takes are a real product type. For that, filter ItemGroup to Furniture, read the values in the ProductType column’s filter list against Table 5.3, which lists the Furniture codes, and clear the filter.

Figure 5.2 shows the Item Table with both new columns.

Figure 5.2: The Item Table with the Product Type Extracted from the Item Code.

Step 3. Assign each invoice to a fiscal period. Charles River’s fiscal year is the calendar year.

1. Go to the SalesInvoice worksheet. In the first empty column after PaymentDate, add a column FiscalYear with the formula =YEAR([@InvoiceDate]).
2. Add a column FiscalQuarter with the formula =ROUNDUP(MONTH([@InvoiceDate])/3,0).
3. Add a column Period with the formula =[@FiscalYear]&"-Q"&[@FiscalQuarter], which returns labels such as 2026-Q4.

Each invoice now carries its fiscal year, its quarter, and a single label that combines them. The label sorts correctly because the year comes first.

Step 4. Measure the payment terms in days. Subtracting the invoice date from the due date gives the number of days a customer has to pay.

1. Add a column DaysToDue with the formula =[@DueDate]-[@InvoiceDate].
2. If Excel formats the result as a date, change the column’s number format to General or Number with no decimals.

Each invoice now shows its payment terms as a whole number of days. Figure 5.3 shows the SalesInvoice Table with the four new columns.

Figure 5.3: The SalesInvoice Table with Fiscal Period and Payment Terms Columns.

Step 5. Group and test the payment terms. Logical functions turn the terms into a label for reporting and a check for review.

1. Add a column TermsGroup with this formula, which places each invoice in one of three bands:

   =IFS([@DaysToDue]<=30,"Up to 30 days",[@DaysToDue]<=60,"31 to 60 days",TRUE,"Over 60 days")

2. Add a column StandardTerms with this formula, which is TRUE when the terms are one of the four that Charles River offers:

   =OR([@DaysToDue]=30,[@DaysToDue]=45,[@DaysToDue]=60,[@DaysToDue]=90)

3. On the Analysis worksheet, type Invoices with nonstandard terms in A39.

4. In B39, enter =COUNTIF(SalesInvoice[StandardTerms],FALSE).

B39 shows zero: every invoice has one of the four standard terms. The test shows that the recorded terms conform; it cannot show whether a due date was calculated by the system or typed by hand, which only the system’s settings or its change records could. Most invoices fall in the two lower bands of TermsGroup, and only a few hundred have terms longer than 60 days.

Step 6. Check the period labels. Open the filter list on the Period column and read the values it shows.

The Period column holds one label for each quarter of the three fiscal years in the data, from 2024-Q1 to 2026-Q4. A label outside that range would point to an invoice dated outside the period of the extract.

Step 7. Record the new columns.

1. On the Documentation worksheet, add the new calculated columns to the Calculated columns entry, each with its formula: ProductType and CodeCheck on Item, and FiscalYear, FiscalQuarter, Period, DaysToDue, TermsGroup, and StandardTerms on SalesInvoice.
2. Add the checks in B39 and B40 to the entry that describes the Analysis worksheet.
3. Save the workbook.

The Documentation worksheet now accounts for every column this tutorial added, so a reviewer can see how each value was calculated.

Checkpoint. Check that:

• every item has a three-letter ProductType, and no item code fails the CodeCheck test;
• every invoice has a Period label from 2024-Q1 to 2026-Q4;
• every invoice has a TermsGroup label, and no invoice fails the StandardTerms test;
• the Documentation worksheet lists the new columns.

The product type and the period are the two attributes the controller’s question needs that the invoice lines do not carry. The next tutorial brings them onto the lines.

5.6 Lookup Functions

A lookup function retrieves a value from one table using a key from another. It is the spreadsheet equivalent of following a foreign key to the row it references, which you practiced by hand in Tutorial 3.2. Each invoice line carries a SalesInvoiceID, and the SalesInvoice Table holds exactly one row for each SalesInvoiceID, with its invoice date, customer, and totals. A lookup finds that row and returns the column you ask for, as Figure 5.4 shows.

Figure 5.4: How XLOOKUP Retrieves a Value from Another Table.

5.6.1 XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) searches the lookup array for the lookup value and returns the value in the same position of the return array. To bring the invoice date onto each invoice line, the formula in a calculated column of the SalesInvoiceLine Table is:

=XLOOKUP([@SalesInvoiceID],SalesInvoice[SalesInvoiceID],SalesInvoice[InvoiceDate],"No match")

The fourth argument is the value returned when the key is not found. Without it, XLOOKUP returns the error #N/A. A text value such as “No match” is easier to count, and counting it is the first check on any lookup: a clean lookup has no unmatched rows. INDEX and MATCH, described next, have no such argument, so a formula built with them is wrapped in IFERROR to return the same text.

5.6.2 INDEX and MATCH, and VLOOKUP

Before XLOOKUP was added to Excel, the same retrieval was written by combining two functions. MATCH(lookup_value, lookup_array, 0) returns the position of the key in a column, and INDEX(return_array, position) returns the value at that position. Together, they do what the XLOOKUP formula above does:

=INDEX(SalesInvoice[InvoiceDate],MATCH([@SalesInvoiceID],SalesInvoice[SalesInvoiceID],0))

The final 0 in MATCH asks for an exact match, which is almost always what accounting keys require. Leave it out, and MATCH looks for an approximate match in a column it assumes is sorted, which can return the wrong row without an error. INDEX and MATCH work in every version of Excel, which matters when you share a workbook with someone whose version of Excel does not have XLOOKUP.

VLOOKUP is the oldest of the three and the most limited. It searches only the first column of a range and returns a column identified by its number, as in =VLOOKUP(key, range, 3, FALSE). Because the key must be in the leftmost column, it cannot look to the left, and because the return column is a number, the formula can return the wrong column if columns are added or removed. You will meet it in older workbooks, and it is worth recognizing, but XLOOKUP is the better choice for new work. Table 5.4 compares the three.

Table 5.4: Lookup functions compared

Feature	VLOOKUP	INDEX and MATCH	XLOOKUP
Where the key can be	Only in the first column of the range	In any column	In any column
How the return column is chosen	By its number in the range	By reference to the column	By reference to the column
Effect of inserting or deleting columns	Can return the wrong column	Adjusts, unless a referenced column is deleted	Adjusts, unless a referenced column is deleted
Match when the match argument is left out	Approximate; FALSE asks for an exact match	Approximate; 0 asks for an exact match	Exact
When the key is not found, in an exact match	#N/A	#N/A	#N/A, or a value you supply
Availability	All versions of Excel	All versions of Excel	Microsoft 365, Excel 2024, and Excel 2021

5.6.3 Lookups Across Several Tables

Some attributes are two keys away. An invoice line does not carry a sales order key, but its invoice does. To find the date on which a line was ordered, first bring the invoice’s SalesOrderID onto the line with one lookup, and then use that SalesOrderID to look up the order date in the SalesOrder Table with a second. Keeping each step in its own column makes the chain easy to check. When two tables share no key at all, such as a supplier and an employee who may share an address, you can build a match key by joining and standardizing the fields they do share, as Exercise 5.5 does.

WarningWatch out

A lookup returns one value, the first match it finds. That is correct when the lookup follows a relationship toward its “one” side, from an invoice line to its invoice or from an invoice to its customer, because the key appears exactly once. It is wrong when it follows a relationship toward the “many” side. A sales order can have several shipments, so a lookup of an order’s shipment date returns the date of whichever shipment comes first in the Table, not necessarily the earliest one. When the relationship is one-to-many, summarize instead: MINIFS returns the earliest shipment date, as in Tutorial 2.1, and SUMIFS or COUNTIFS return totals and counts. Chapter 3’s discussion of cardinality tells you which side of a relationship you are on.

NoteConnecting the Dots

The lookups of the tutorials follow a foreign key to a primary key, the relationship you studied in Chapter 3; the match key of Exercise 5.5 is the kind you build when no such relationship exists. In SQL, a join combines two tables in a query result, much as a lookup brings one table’s values onto another’s rows, while a relationship in Power BI’s data model keeps the tables separate and lets a filter on one reach the other without copying columns. The skill of asking which key connects two tables, and on which side of the relationship each table sits, carries over to both of the tools you will learn in Parts III and IV.

5.7 Guided Tutorial 5.2: Bringing Dates and Groups onto the Invoice Lines

Context and objective. In this tutorial, you will use lookups to bring the attributes the controller’s question needs onto each invoice line: the invoice date and period from the invoice, the order date from the sales order, and the item group and product type from the item. You will check that every lookup found its match, reconcile the Furniture revenue by quarter to the totals you reconciled in the Part I case, and find the promotion lines that were invoiced after the promotion ended.

Prerequisites. You need the analysis workbook from Tutorial 5.1, with the ProductType column on the Item Table and the Period column on the SalesInvoice Table. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 5.1 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Import the sales orders. The order date lives in the SalesOrder Table, which the workbook does not have yet. Import it as you imported the other Tables in Chapter 4.

1. Choose Data > Get Data > From File > From Excel Workbook, select CharlesRiver.xlsx, and select the T9_SalesOrder Table in the Navigator. Click Transform Data.
2. Rename the query SalesOrder, and use Home > Choose Columns to keep SalesOrderID, OrderNumber, OrderDate, CustomerID, and Status.
3. Set the OrderDate column’s data type to Date if it shows a time.
4. Choose Home > Close & Load, and rename the new worksheet SalesOrder.

The workbook now has a SalesOrder Table with one row for each sales order, keyed by SalesOrderID.

Step 2. Bring the invoice date and period onto each line. Each invoice line carries the key of its invoice, SalesInvoiceID, so one lookup per column brings the invoice’s attributes onto the line.

1. Go to the SalesInvoiceLine worksheet. In the first empty column after DiscountAmount, the last calculated column you added in Chapter 4, add a column InvoiceDate with this formula, and format the column as a date:

   =XLOOKUP([@SalesInvoiceID],SalesInvoice[SalesInvoiceID],SalesInvoice[InvoiceDate],"No match")

2. Add a column Period with the same formula, returning SalesInvoice[Period] instead of SalesInvoice[InvoiceDate].

Every line now shows the date and period of its invoice, so the lines can be totaled by quarter.

Step 3. Follow the key from the invoice to the order. The order date is two keys away: the line leads to its invoice, and the invoice leads to its order.

1. Add a column SalesOrderID that returns SalesInvoice[SalesOrderID] with the same lookup as in Step 2.

2. Add a column OrderDate with the formula below, and format it as a date. It uses INDEX and MATCH, the form that works in every version of Excel, wrapped in IFERROR so that a missing order returns “No match” as the XLOOKUP formulas do:

   =IFERROR(INDEX(SalesOrder[OrderDate],MATCH([@SalesOrderID],SalesOrder[SalesOrderID],0)),"No match")

The line now shows when its order was placed. The OrderDate column is the end of a two-step chain, from the line to its invoice and from the invoice to its order, and each step sits in its own column where you can check it.

Step 4. Bring the item group and product type onto each line.

1. Add a column ItemGroup with this formula:

   =XLOOKUP([@ItemID],Item[ItemID],Item[ItemGroup],"No match")

2. Add a column ProductType with the same formula, returning Item[ProductType].

Every line now carries its item group and product type, the two attributes the Furniture question is organized by.

Step 5. Check that every lookup found its match. A lookup that fails returns “No match”, so counting that value tests every lookup.

1. On the Analysis worksheet, type Lookups not matched in A41.

2. The six new columns sit side by side, so one COUNTIF can search them all. In B41, enter this formula, which uses a structured reference to the range of columns from InvoiceDate to ProductType:

   =COUNTIF(SalesInvoiceLine[[InvoiceDate]:[ProductType]],"No match")

B41 should show zero. Every invoice line found its invoice, every invoice found its order, and every item sold is among the items you kept in Chapter 4, so the lookups are complete. Each key is also the primary key of the Table it is looked up in, so each lookup found the one row its key can match, not merely the first of several.

Step 6. Reconcile the Furniture revenue by quarter. The lines now carry a period and an item group, so their revenue can be summed by both.

1. On the Analysis worksheet, type Furniture revenue by quarter in A43, and type the labels 2026-Q1, 2026-Q2, 2026-Q3, and 2026-Q4 in B44 through E44.

2. In B45, enter this formula, then copy B45 and paste it into C45:E45 (copy and paste rather than drag, as Chapter 4 explained):

   =SUMIFS(SalesInvoiceLine[LineTotal],SalesInvoiceLine[ItemGroup],"Furniture",SalesInvoiceLine[Period],B$44)

3. Compare the third and fourth quarters with the Furniture revenue you reconciled to the ledger in the Part I case.

The totals should agree to the cent. The agreement shows that the lookups reproduce, quarter by quarter, the Furniture revenue you reconciled to the ledger. It is evidence at the level of those totals, not proof that every line has the right group and quarter: as with the control totals of Tutorial 4.1, Step 10, errors that offset within a total would leave it unchanged.

Step 7. Find the promotion lines invoiced after the promotion ended.

1. On the SalesInvoiceLine worksheet, use the PromotionID slicer from Chapter 4 to show promotion 8, the Furniture Seasonal Promotion.

2. Open the filter list on the InvoiceDate column, choose Date Filters > After, and enter 2026-10-31, the promotion’s end date.

3. Read the status bar, and look at the OrderDate column of the lines in view.

4. On the Analysis worksheet, type Promotion 8 lines invoiced after its end date in A47.

5. In B47, enter this formula, which counts the same lines without the filters:

   =COUNTIFS(SalesInvoiceLine[PromotionID],8,SalesInvoiceLine[InvoiceDate],">"&DATE(2026,10,31))

6. Clear both the InvoiceDate filter and the PromotionID slicer.

More than a hundred promotion lines were invoiced in November and December, after the promotion ended, and every one of them comes from an order placed while the promotion was running. The promotional price was set when the order was placed, but the revenue was recorded when the order was invoiced. Figure 5.5 shows some of these lines.

Figure 5.5: Invoice Lines with Their Looked-Up Dates and Groups.

Step 8. Record the new Table and columns.

1. On the Documentation worksheet, add T9_SalesOrder to the Source entry and the SalesOrder query to the Query steps entry.
2. Add the six lookup columns on SalesInvoiceLine to the Calculated columns entry: InvoiceDate, Period, SalesOrderID, OrderDate, ItemGroup, and ProductType.
3. In the Open questions entry, replace the sentence that says the lines carry no invoice date or item group with the next question, how much of the change in the Furniture margin the promotion explains.
4. Add the checks in rows 41 to 47 of the Analysis worksheet to the entry that describes it, and save the workbook.

A reviewer can now trace every new column on the invoice lines to the Table it came from and the key it followed.

Checkpoint. Check that:

• the SalesInvoiceLine Table has six new lookup columns, and none of them contains “No match”;
• the Furniture revenue for 2026-Q3 and 2026-Q4 agrees with the Part I case;
• you have counted the promotion 8 lines invoiced after the promotion’s end date;
• the Documentation worksheet lists the SalesOrder Table and the six new columns.

The last finding changes how the controller’s question should be read. The promotion’s effect on the fourth quarter depends on when its orders were invoiced, not only on its effective dates, so a quarter-by-quarter analysis must use the invoice date for revenue and keep the order date in view to explain it.

5.8 Missing, Meaningful, and Invalid Values

Chapter 2 introduced missing values and duplicate records as data quality problems. Preparation adds a practical distinction: a blank cell does not always mean that information is missing. In the Charles River invoice lines, a blank PromotionID means that the line carried no promotion, and a blank ShipmentLineID on a design-service line means that nothing was shipped. These are meaningful blanks. They record a fact, and replacing them or treating them as errors would be wrong. A blank TrackingNumber on a shipment, as you found in Tutorial 2.1, is different: the information should exist and does not. Before you fill, remove, or flag blanks, decide which kind they are, and record the decision.

Formulas complicate the test for blanks. ISBLANK returns TRUE only for a cell that contains nothing at all, so it returns FALSE for a cell whose formula returns an empty text value, even though the cell looks empty. To find the rows a lookup failed to match, count the value you told the lookup to return, such as “No match”, rather than testing for blanks.

A value can also be present and still be invalid for its purpose. A validity check tests that each value has the form or range it should. Charles River sells most items by the unit, recorded in the Item Table’s UnitOfMeasure column as Each, so you might expect every quantity for those items to be a whole number. The test =[@Quantity]<>INT([@Quantity]), which returns TRUE for a quantity with a fractional part, finds that most invoice lines for items sold by the unit carry fractional quantities, such as the 3.74 sideboards billed on invoice line 10646.

In practice, a finding like this goes to the owners of the source system with a question, because the cause could be a partial-unit shipping practice, a unit-of-measure setting, or an error in how quantities are recorded. For the analysis, what matters is whether the finding changes the answer. Revenue, list amounts, and discounts are recorded on each line and are consistent with one another, and an average price calculated as revenue divided by quantity is consistent across periods. Record the finding in the documentation as a known limitation of the quantity data, and avoid conclusions that depend on whole units.

Duplicates are the last familiar problem. Excel’s Remove Duplicates command, on the Data tab, deletes rows that repeat the values of the columns you choose. Use it with care: it deletes without asking which row to keep, and two rows that look the same may record two genuine transactions. As in Chapter 2, count and examine suspected duplicates before removing anything, and remove them in a query or a copy, never in the source data.

5.9 Power Query for Repeatable Preparation

The lookup columns you add in Tutorial 5.2 work, but they have two weaknesses for a lasting analysis. They live as formulas in the loaded Table, so every refresh recalculates thousands of lookups, and the preparation they perform is spread across several columns rather than recorded as a single procedure. Power Query, which you used in Chapter 4 to import the data, can perform the same preparation as a recorded sequence of steps that runs whenever the data is refreshed.

5.9.1 Merging Queries

Power Query’s equivalent of a lookup is a merge. Merge Queries, on the Home tab of the Power Query Editor, combines two queries by matching a key column in each, and adds the matching rows of the second query as a new column that you then expand into the columns you need. When you expand it, Power Query offers to prefix each new column’s name with the name of the query it came from, as in Item.ItemGroup. The tutorials in this book always clear the Use original column name as prefix check box, so that each column keeps its own name and later formulas can refer to it directly. A merge has a join kind, which decides which rows appear in the result. Table 5.5 lists the join kinds you will use most.

Table 5.5: Join kinds in Power Query merges

Join kind	Rows in the result	Accounting use
Left outer	Every row of the first query, with the matching rows of the second	Adding invoice and item details to every invoice line
Inner	Only the rows that match in both queries	Keeping only lines whose item appears in a filtered item list
Full outer	Every row of both queries, matched where possible	Comparing two lists, such as a sub-ledger and a ledger extract
Left anti	Only the rows of the first query that have no match in the second	Finding orphaned records, such as lines whose item is missing

For enriching the invoice lines, left outer is the right choice, because every invoice line must stay in the result whether or not its match is found. A left anti merge on the same keys is a useful check: it returns the lines that failed to match, which should be none. A left outer merge repeats a row of the first query for each row of the second that matches it, so the row count stays the same only when no row has more than one match, as with the Charles River keys, each of which appears once in its Table. An unchanged row count therefore shows that no row was repeated, not that every row found a match, because a row without one stays in the result with empty columns. The message at the bottom of the Merge dialog helps you choose the right columns, but Power Query calls its count of matches an estimate, so the tests use the loaded result instead.

5.9.2 Custom Columns

A custom column, added through Add Column > Custom Column, calculates a new column with a formula written in Power Query’s formula language, called M. The functions differ from Excel’s, but the ideas are the same. Date.Year([InvoiceDate]) returns the year, Date.QuarterOfYear([InvoiceDate]) returns the quarter, and Text.Middle([ItemCode], 4, 3) returns three characters of the item code starting after the fourth. Note that Text.Middle counts positions from zero, where Excel’s MID counts from one, so the same product type code starts at position 4 in M and position 5 in Excel. As in Chapter 4, every custom column appears in the Applied Steps list, so the query records exactly how each value was calculated. The Conditional Column command, also on the Add Column tab, builds the Power Query equivalent of IF through a dialog: if a column meets a condition, then one value, else another. It writes the M formula if ... then ... else for you.

5.9.3 Reference Queries and Refresh

A merge can change the query it is applied to, so it is good practice to build the combined table in a new query rather than altering the queries that load the source Tables. Right-clicking a query in the Queries pane and choosing Reference creates a new query that starts from the result of the first one. The original query is unchanged, and the new query is rebuilt from the original’s result every time the workbook is refreshed. When the data is refreshed, Power Query runs the source queries and then every step of the combined query, so the combined table is rebuilt from the current data without any formula being copied down.

TipIn Practice

Junior auditors are routinely asked to prepare client data for testing. In a teaching case built around that task, Hunter et al. (2020) describe cleaning large data sets as a vital task that is routinely performed by the most junior member of the audit team, and they pair the cleaning with checks that the prepared data is complete and accurate. A preparation workflow that records its steps, reconciles its row counts and totals to the source, and can be rerun on the next extract gives a reviewer part of that evidence. Whether the source data are complete and reliable can still need evidence from outside the workflow.

5.10 Guided Tutorial 5.3: Building InvoiceLines in Power Query

Context and objective. In this tutorial, you will build the table that Chapters 6 and 7 analyze. Instead of adding more formula columns, you will create a new Power Query query, InvoiceLines, that starts from the invoice lines, merges in the invoice, item, and customer details, and calculates the fiscal period, the product type, and a quantity check as custom columns. You will load it as a Table, reconcile it with the source and with the lookups from Tutorial 5.2, and record it on the Documentation worksheet. Because it is a query, it rebuilds itself whenever you refresh the workbook.

Prerequisites. You need the analysis workbook from Tutorials 5.1 and 5.2, including the SalesInvoice, SalesInvoiceLine, and Item queries. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 5.2 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Open the Power Query Editor and import the customers. The customer segment and region live in the Customer Table, which the workbook does not have yet.

1. Choose Data > Get Data > Launch Power Query Editor.
2. In the editor, choose Home > New Source > File > Excel Workbook, select CharlesRiver.xlsx, and select the T4_Customer Table in the Navigator. Click OK.
3. Rename the new query Customer, and use Home > Choose Columns to keep CustomerID, CustomerName, CustomerSegment, and Region.

The Queries pane now lists Customer below the queries you created in Chapter 4 and Tutorial 5.2. It will load as its own Table when you close the editor in Step 6.

Step 2. Start the InvoiceLines query from the invoice lines.

1. In the Queries pane, right-click SalesInvoiceLine and choose Reference.
2. Rename the new query InvoiceLines.

The new query starts from the result of SalesInvoiceLine, which stays unchanged. It holds the invoice lines as Power Query loads them, without the calculated columns you added to the worksheet in Chapter 4 and Tutorial 5.2.

Step 3. Merge in the invoice details.

1. With InvoiceLines selected, choose Home > Merge Queries.
2. In the upper table, click the SalesInvoiceID column. In the drop-down list below it, choose SalesInvoice, and click its SalesInvoiceID column.
3. Leave the Join Kind set to Left Outer (all from first, matching from second), and read the message at the bottom of the dialog, which estimates how many of the invoice lines will find a match. Click OK.
4. A new column appears at the right, with a Table in every row. Click the expand button in its header, clear Select All Columns, select InvoiceDate, SalesOrderID, and CustomerID, clear Use original column name as prefix, and click OK.

Figure 5.6 shows the Merge dialog.

Figure 5.6: The Merge Dialog in Power Query.

Step 4. Merge in the item and customer details. Repeat the merge for the two other Tables.

1. Merge InvoiceLines with Item on ItemID, with a left outer join, and read the match message before you click OK.
2. Expand ItemCode, ItemGroup, and StandardCost, without the prefix.
3. Merge InvoiceLines with Customer on CustomerID, the column you added in Step 3, with a left outer join, and read the match message.
4. Expand CustomerSegment and Region, without the prefix.

Each match message estimates that every row of the first table finds a match. Because every key refers to exactly one row of the Table it is matched in, the merges add columns without adding or losing rows. The match message is only an estimate, and the editor’s status bar shows only a preview of the rows, so Step 6 checks the loaded result.

Step 5. Add the calculated columns. Custom columns calculate the period, the product type, and the quantity check inside the query, so that they are rebuilt on every refresh.

1. Choose Add Column > Custom Column.
2. Type the first name in Table 5.6 in New column name and its formula in Custom column formula, and click OK.
3. Repeat the two actions for each of the other columns in the table.
4. Set each new column’s data type with the icon at the left of its header: whole number for FiscalYear and FiscalQuarter, text for Period and ProductType, and true/false for WholeQuantity.

Table 5.6: Custom columns for the InvoiceLines query

New column name	Custom column formula
FiscalYear	Date.Year([InvoiceDate])
FiscalQuarter	Date.QuarterOfYear([InvoiceDate])
Period	Text.From([FiscalYear]) & "-Q" & Text.From([FiscalQuarter])
ProductType	Text.Middle([ItemCode], 4, 3)
WholeQuantity	[Quantity] = Number.RoundDown([Quantity])

Two of the M functions need a word of explanation. Text.From converts a number to text, so that the year and the quarter can be joined with “-Q” into a label, and Number.RoundDown rounds a number down to the whole number at or below it, so a quantity equals its rounded-down value, and WholeQuantity is true, only when the quantity is already a whole number. Figure 5.7 shows the dialog for the ProductType column.

Figure 5.7: The Custom Column Dialog in Power Query.

Step 6. Load InvoiceLines and check the result.

1. Choose Home > Close & Load > Close & Load To, select Table and New worksheet, and click OK. The choice applies to both new queries, as in Tutorial 4.1, Step 7, so Power Query loads InvoiceLines and Customer into two new worksheets as Tables. Rename the worksheets InvoiceLines and Customer.

2. On the Analysis worksheet, type InvoiceLines rows in A49 and InvoiceLines line total in A50, and enter =COUNTA(InvoiceLines[SalesInvoiceLineID]) in B49 and =SUM(InvoiceLines[LineTotal]) in B50. Compare them with the invoice line count in B3 and the line total in B5.

3. In A52, type Furniture revenue from InvoiceLines. In B52, enter the formula below, and copy and paste it into C52:E52:

   =SUMIFS(InvoiceLines[LineTotal],InvoiceLines[ItemGroup],"Furniture",InvoiceLines[Period],B$44)

4. In A53, type Lines with a fractional quantity, and in B53 enter:

   =COUNTIF(InvoiceLines[WholeQuantity],FALSE)

B49 and B50 equal the invoice line count and line total from Chapter 4, and row 52 repeats row 45 to the cent, so the query and the lookups agree. Like the control totals of Tutorial 4.1, Step 10, the count and total cannot show that every key matched, because a left outer merge keeps a line that finds no match. A left anti merge on each key that returns no rows would show it, and a key that appears only once in the second Table, as each of these does, is what keeps a line from being repeated. B53 shows that most lines carry a fractional quantity. It also counts the design services billed by the hour, but most of the lines it flags are for items sold by the unit, the validity finding described earlier in this chapter. Figure 5.8 shows the loaded Table.

Figure 5.8: The InvoiceLines Table Loaded by Power Query.

Step 7. Record the query and refresh.

1. On the Documentation worksheet, add the InvoiceLines and Customer worksheets to the Worksheets entry, and describe their steps in the Query steps entry: Customer keeps four columns, and InvoiceLines is a reference to SalesInvoiceLine with left outer merges with SalesInvoice, Item, and Customer, and five custom columns.
2. Add the checks in rows 49 to 53 of the Analysis worksheet to the entry that describes it.
3. Add the fractional quantities to the Open questions entry as a known limitation of the quantity data.
4. Add T4_Customer to the Source entry.
5. Choose Data > Refresh All, and confirm that the row counts and totals are unchanged.
6. Update the Last refreshed date, and save the workbook.

The refresh reruns every recorded step on the source Tables. Unchanged counts and totals show that these controls agree after the refresh, not that every value in InvoiceLines is the same as before or right.

Checkpoint. Check that:

• InvoiceLines has exactly as many rows as SalesInvoiceLine, and the same line total;
• its Furniture revenue for each quarter of 2026 agrees with the lookups from Tutorial 5.2;
• most lines are flagged as having a fractional quantity, and the Documentation worksheet records it;
• a refresh rebuilds InvoiceLines without any change to the totals.

The two methods agree. Both start from the same keys and dates, so their agreement shows that the query prepares the data as consistently as the lookups, not that every source value is right; and the Furniture totals test the item group and the quarter, not the customer segment and region that only the query adds. From here on, the analysis uses InvoiceLines, because it is a single recorded procedure that runs again on every refresh, while the lookup columns on SalesInvoiceLine remain in the workbook as its cross-check. The order date stays on SalesInvoiceLine, for the analyses that need it.

5.11 Looking Ahead

This chapter has prepared the Charles River invoice lines for analysis. You have used text functions to extract a product type from an item code and learned when standardizing text helps and when it harms. You have assigned invoices to fiscal periods, measured the time between orders and invoices, and seen why an as-of date is better than TODAY. You have written logical tests with IF, IFS, AND, and OR to classify and check values, and used IFERROR to handle a lookup that fails. You have combined Tables with lookups, followed a key through two tables, and learned to count the rows a lookup failed to match. You have distinguished meaningful blanks from missing information and recorded a validity finding about quantities. Finally, you have built the same enrichment in Power Query as a refreshable InvoiceLines table and reconciled it with your lookups and with the source.

The preparation has already sharpened the controller’s question. The Furniture Seasonal Promotion priced orders placed in September and October, but many of those orders were invoiced after the promotion ended, so its effect reaches further into the fourth quarter than its dates suggest. InvoiceLines now holds, for every invoice line, the period, the product group and type, and the customer segment that an answer requires. Chapter 6 summarizes that table and uses it to explain the change in the Furniture margin, and Chapter 7 builds on the result.

5.12 Chapter Summary

Data preparation transforms data from the form in which it was recorded into the form an analysis needs. In accounting, it usually means bringing attributes that live in related tables onto the transactions being analyzed. A preparation error changes every number that follows without announcing itself. Every preparation step should therefore be paired with a check, such as a count of unmatched rows or a total that must agree with its source, and the whole procedure should be documented so that someone else can repeat it.

Text functions extract, standardize, and validate text fields. MID, LEFT, RIGHT, FIND, and LEN extract the parts of a structured value, such as the product type in a Charles River item code. TRIM, CLEAN, SUBSTITUTE, UPPER, LOWER, and PROPER standardize text. Apply them only after a test shows that a field needs them, because standardizing clean data can damage it, as PROPER does to a name such as Donaldson LLC. Date functions assign transactions to fiscal periods and measure the time between events. A non-calendar fiscal year needs an adjusted quarter formula, and a fixed as-of date is more reliable than TODAY for any measure of age.

Logical functions classify and test values. IF returns one of two values depending on a test, IFS returns the value for the first of several tests that is true, and AND, OR, and NOT combine tests. IFERROR substitutes a value when a formula returns an error, which is useful when the substitute can be counted, such as “No match”.

Lookup functions retrieve a value from one table using a key from another. XLOOKUP is the most flexible, INDEX and MATCH work in every version of Excel, and VLOOKUP is limited to keys in the first column. A lookup is correct when it follows a relationship toward its “one” side. Toward the “many” side it returns only the first match, and MINIFS, SUMIFS, and COUNTIFS are the right tools. Counting the rows a lookup failed to match is the first check on any lookup. Preparation also separates blanks that record a fact, such as a missing PromotionID on a line with no promotion, from blanks that mean information is missing. It also checks that values are valid for their purpose: the fractional quantities on items sold by the unit fail that check, a finding to take to the owners of the source system rather than proof that the quantities are wrong.

Power Query performs the same preparation as a recorded, refreshable procedure. A merge combines two queries on a key column, and its join kind, usually left outer, decides which rows appear. Custom columns calculate new values with Power Query’s formula language. Applied to the Charles River data, these tools produced InvoiceLines, a single table in which every invoice line carries its fiscal period, product group and type, and customer segment. Its totals agree with the source and with the lookups, which shows that the two methods prepare the data consistently, although agreement at the level of totals cannot show that every joined value is right. Preparing it also showed that many lines of the Furniture Seasonal Promotion were invoiced after the promotion ended.

5.13 Key Terms

As-of date. A fixed date, usually stored in a named parameter cell, from which ages and elapsed times are measured. Unlike TODAY, an as-of date does not change when the workbook recalculates on a later day, so the calculation can be reproduced and reviewed.

Custom column. A column added to a Power Query query and calculated with a formula in Power Query’s formula language, M. Custom columns are recorded as applied steps and recalculated on every refresh.

Data preparation. The work of transforming data from the form in which it was recorded into a form suitable for an analysis, including profiling, combining, restructuring, cleaning, and validating it. It is the third stage of the analytics workflow.

Fiscal period. The accounting period, such as a fiscal year, quarter, or month, to which a transaction is assigned. Charles River’s fiscal year is the calendar year, and its fiscal quarters run from January through March, April through June, and so on.

INDEX and MATCH. A pair of Excel functions used together to look up a value: MATCH returns the position of a key in a column, and INDEX returns the value at that position in another column. The combination works in every version of Excel.

Join kind. The rule that decides which rows appear in the result of a merge, such as left outer, inner, full outer, or left anti. The join kind determines whether rows without a match are kept, dropped, or returned on their own.

Left outer join. A join kind that keeps every row of the first table and adds the matching rows of the second, leaving the added columns empty where no match exists. It is the usual choice for enriching transactions with details from related tables.

Logical function. An Excel function that works with logical tests, expressions that are either TRUE or FALSE. IF and IFS return a value that depends on a test, AND, OR, and NOT combine or reverse tests, and IFERROR substitutes a value when a formula returns an error. Logical functions classify values into groups and flag the rows that need attention.

Lookup function. An Excel function, such as XLOOKUP or VLOOKUP, that retrieves a value from one table using a key from another. A lookup is the spreadsheet equivalent of following a foreign key to the row it references.

Match key. A value built to match records that do not share an identifier, usually by joining and standardizing several columns, such as an address and a city. Match keys are used to compare lists from different sources.

Meaningful blank. An empty value that records a fact rather than missing information, such as a blank PromotionID on an invoice line that carried no promotion. Meaningful blanks should be kept, not filled or flagged as errors.

Merge. A Power Query operation that combines two queries by matching a key column in each and adds the matching rows of the second query as columns of the first. It is Power Query’s equivalent of a lookup or a SQL join.

Reference query. A Power Query query created from another query with the Reference command, which starts from the other query’s result and is rebuilt from it on every refresh. Building a combined table as a reference query leaves the source queries unchanged.

Text function. An Excel function that extracts, changes, or tests text, such as MID, TRIM, SUBSTITUTE, or PROPER. Text functions are used to pull attributes out of codes and to standardize values so that they match.

Validity check. A test that each value has the form or range it should for its purpose, such as an item code of the expected length or a quantity that is a whole number. A validity check can fail even when the value is present and accurately recorded.

XLOOKUP. An Excel function that searches one column for a key and returns the value in the same position of another column, with an optional value to return when the key is not found. It is available in Excel for Microsoft 365, Excel 2024, and Excel 2021.

5.14 Multiple Choice Questions

1. A Charles River item code has the form GRP-TYP-9999, such as FUR-DSK-0075. What does =MID("FUR-DSK-0075",5,3) return?

1. FUR
2. -DS
3. 0075
4. DSK

2. An analyst applies PROPER to a list of supplier names to standardize them for a report. What happens to the name Donaldson LLC?

1. It stays Donaldson LLC, because PROPER leaves any word in capital letters unchanged.
2. It becomes Donaldson Llc, because PROPER lowers each letter after a word’s first.
3. It becomes DONALDSON LLC, because PROPER converts names to capitals.
4. It returns an error, because PROPER cannot process abbreviations.

3. A company’s fiscal year begins on April 1. To which fiscal quarter does an invoice dated February 10 belong?

1. The fourth quarter
2. The second quarter
3. The third quarter
4. The first quarter

4. An auditor wants to measure how many days old each open invoice is. Why is a fixed as-of date in a named cell better than =TODAY()?

1. TODAY cannot be used in a calculated column of an Excel Table.
2. TODAY returns the date on which the workbook was first created.
3. A later recalculation changes TODAY, but not an as-of date.
4. An as-of date makes the calculation of each age run faster.

5. Consider this formula:

=XLOOKUP([@SalesInvoiceID],SalesInvoice[SalesInvoiceID],SalesInvoice[InvoiceDate],"No match")

What does its fourth argument do?

1. It names the column that holds the key.
2. It supplies the value returned when the key is not found.
3. It forces an approximate match.
4. It limits the search to the first row that matches the key.

6. Each invoice line carries a SalesInvoiceID, and each invoice carries a SalesOrderID. How can you bring the order date onto each invoice line?

1. Look up the order date directly with the line’s SalesInvoiceID in the SalesOrder Table.
2. Use VLOOKUP on the SalesOrder Table, because XLOOKUP cannot search more than one Table.
3. Sort both Tables by date and copy the order dates across to the invoice lines.
4. Look up the invoice’s SalesOrderID first, and then use it to look up the order date.

7. A sales order can have several shipments. An analyst wants to bring the earliest shipment date onto each order and uses XLOOKUP. What is the problem, and what is the better approach?

1. XLOOKUP cannot return dates from another Table; use VLOOKUP instead.
2. XLOOKUP returns the sum of the shipment dates; use AVERAGEIFS instead.
3. XLOOKUP returns the first match, not always the earliest; use MINIFS instead.
4. There is no problem, because XLOOKUP always returns the earliest shipment date.

8. A cell contains a formula that returns an empty text value, so the cell looks blank. What does the ISBLANK function return for that cell?

1. FALSE, because the cell contains a formula
2. TRUE, because the formula returns no value
3. An error, because ISBLANK cannot test formulas
4. TRUE, because the cell looks blank

9. Which of the following is a meaningful blank in the Charles River data?

1. A blank CustomerID on a sales order
2. A blank TrackingNumber on a shipment that was delivered
3. A blank InvoiceDate on an invoice
4. A blank PromotionID on a line that had no promotion

10. An analyst wants a list of the invoice lines whose ItemID does not appear in the Item query. Which Power Query join kind returns exactly those lines?

1. Left outer
2. Inner
3. Left anti
4. Full outer

11. After a left outer merge in Power Query and the expansion of the merged column, a query has more rows than it had before the merge. What is the most likely explanation?

1. The join kind was changed to left anti after the merge.
2. The key appears more than once in the second table.
3. The first table contained blank keys that matched every row.
4. Power Query adds a header row to the result of every merge.

12. In Power Query, Text.Middle([ItemCode], 4, 3) returns the same product type code as Excel’s =MID([@ItemCode],5,3). Why do the starting positions differ?

1. Text.Middle counts positions from zero, and MID counts from one.
2. Excel’s MID always skips the first character of the text.
3. Text.Middle reads the code from right to left, not left to right.
4. Power Query counts the hyphens in a code differently from Excel.

13. What is the main advantage of building InvoiceLines as a reference query instead of adding merge steps to the SalesInvoiceLine query itself?

1. Reference queries cannot be refreshed, so their results are fixed.
2. Reference queries run even when the source workbook, CharlesRiver.xlsx, is not available.
3. The original query is unchanged, and the new one uses its result on refresh.
4. A reference query copies the worksheet formulas of the original Table.

14. A calculated column on the invoice lines uses this formula, where PromoEnd is a named cell that holds the date 2026-10-31:

=IF(AND([@Discount]>0,[@InvoiceDate]>PromoEnd),"Late","OK")

What does it return for a line with a 10 percent discount that was invoiced on 2026-10-15?

1. “OK”, since AND needs both tests true
2. “Late”, because the line carries a discount
3. TRUE, because AND returns TRUE or FALSE
4. An error, because AND cannot compare dates

15. A validity check finds that most invoice lines for items sold by the unit carry fractional quantities. What is the most appropriate response?

1. Round every quantity to the nearest whole number before analyzing any of the lines.
2. Record it as a limitation, ask the system’s owners, and avoid relying on whole units.
3. Delete the lines with fractional quantities from the analysis so that the totals are clean.
4. Ignore the finding, because quantities do not affect the revenue recorded on each line.

5.15 Applied Exercises

As in Chapter 4, work on these exercises in a fresh copy of your analysis workbook, made after you finish this chapter’s tutorials, or in a new workbook where an exercise says so. Put each exercise on its own worksheets, named after it, and do not rename or edit the queries the tutorials created.

5.15.1 Financial Accounting Exercises

Exercise 5.1: Net Furniture Revenue by Quarter

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. You are a financial reporting analyst at Charles River. The InvoiceLines table shows what was billed, but customers returned some goods, and the controller wants the Furniture revenue for fiscal 2026 net of those returns. The returns are recorded as credit memos, whose lines have the same structure as invoice lines.

Requirements.

1. Use Power Query to load the CreditMemo and CreditMemoLine Tables from CharlesRiver.xlsx into your copy of the analysis workbook, with CreditMemoDate set to the Date type. Load both as Tables on new worksheets, with Close & Load To and then Table and New worksheet, as in Tutorial 4.1, Step 7.

2. Prepare the credit memo lines for the comparison. Bring each line’s CreditMemoDate from the CreditMemo Table, matching on CreditMemoID, and add a period label. Bring the ItemGroup from the Item Table, matching on ItemID. Use either lookups or Power Query merges, and count the rows that failed to match.

3. Build a grid with the four quarters of 2026 across a row and three rows below them: Furniture revenue billed, from the LineTotal of InvoiceLines (after line discounts and before credit memos), Furniture credit memo lines, and net Furniture revenue. Fill it with SUMIFS formulas.

4. Some credit memos in 2026 reverse invoices issued in an earlier year. Use the credit memo’s OriginalSalesInvoiceID to find them, and explain in two or three sentences whether a credit should be assigned to the quarter of the credit memo or to the quarter of the original invoice for this analysis.

Deliverable. A quarterly schedule of billed and net Furniture revenue for fiscal 2026 with a short note on the timing of credits.

Exercise 5.2: Testing Document Numbers Against Dates

Table group: Order-to-Cash

Scenario. You are a financial reporting analyst preparing the year-end close. Part of the close is a revenue cutoff review, which checks that every invoice is recorded in the fiscal year in which the sale was made. The finance team uses the year in each invoice number, such as the 2025 in SI-2025-007947, as a quick check of an invoice’s fiscal year, and the controller asks you to test whether that is reliable.

Requirements.

1. On the SalesInvoice Table, add a calculated column that extracts the year from the InvoiceNumber with a text function, and a second column that converts it to a number.

2. Add a column that compares the number’s year with the year of the InvoiceDate, and count the invoices where they differ.

3. Use the SalesOrderID to bring each invoice’s OrderDate onto the SalesInvoice Table, add a column that flags invoices dated before their order, and count them.

4. List the invoices flagged by either test. Compare the list with the invoices dated before their first shipment that you found in Tutorial 2.1, which are still in CharlesRiver_Work.xlsx, your Part I working copy: filter the Check column of its SalesInvoice worksheet for “Check.” Describe what the three findings together suggest about how these invoices were dated.

5. Write a brief memo (one half page) to the controller that explains the tests, the results, the effect the findings could have on revenue cutoff, and the evidence you would request from the billing team.

Deliverable. A SalesInvoice Table with the test columns and a half-page memo on the invoices whose numbers or dates are inconsistent.

5.15.2 Managerial Accounting Exercises

Exercise 5.3: The Furniture Product Mix

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. You are a management accountant supporting the controller’s margin analysis. Before measuring the causes of the margin change, she wants to know whether the mix of Furniture products sold changed between the third and fourth quarters of fiscal 2026.

Requirements.

1. Using InvoiceLines, build a grid with the eight Furniture product types as rows and, for 2026-Q3 and 2026-Q4, columns for revenue and quantity. InvoiceLines holds the product type codes, so put the codes in one column, for the SUMIFS criteria, and their names from Table 5.3 in another, as labels. Include ItemGroup as a criterion too, because some codes, such as TBL, also occur in other groups.

2. Add columns for each product type’s share of Furniture revenue in each quarter, and for the change in share in percentage points: the fourth quarter’s share less the third quarter’s.

3. Apply conditional formatting that highlights the product types whose share changed by more than two percentage points.

4. Write three to four sentences describing how the mix changed. Explain why quantities should be interpreted with care in this data, and why revenue shares are the more reliable measure of mix here.

Deliverable. A product mix grid for the Furniture group in 2026-Q3 and 2026-Q4, with a short interpretation.

Exercise 5.4: How Long Orders Take to Be Invoiced

Table group: Order-to-Cash

Scenario. The controller has learned from Tutorial 5.2 that promotional orders can be invoiced after a promotion ends. She asks how long orders normally take to be invoiced, and whether the lag is stable from quarter to quarter, before she decides how to treat promotion timing in the margin analysis.

Requirements.

1. On the SalesInvoice Table, bring each invoice’s OrderDate from the SalesOrder Table, unless you already added it in Exercise 5.2, and calculate the number of days from order to invoice.

2. For each quarter of fiscal 2026, use COUNTIFS and AVERAGEIFS to calculate the number of invoices and the average lag. Use MEDIAN to calculate the median lag of all the invoices, and explain why the average is higher than the median. Describe how stable the lag is from quarter to quarter, and note any invoice with a negative lag, dated before its order.

3. For each promotion in the PromotionProgram Table, count its invoice lines and the lines invoiced after its EffectiveEndDate. Before you read the counts as billing lag, check each promotion’s dates: a promotion whose end date comes before its start date, or that runs for a single day, raises a question about the master data rather than about billing.

4. Write a brief memo (three to five sentences) recommending whether the margin analysis should assign promotional revenue by invoice date or by order date, and why.

Deliverable. A lag analysis by quarter, a count of promotion lines invoiced after each promotion ended, and a short recommendation.

5.15.3 Auditing Exercises

Exercise 5.5: Testing for Related-Party Suppliers

Table group: Procure-to-Pay, with the Employee table from Master Data

Scenario. As part of the audit of purchasing, your senior asks you to test whether any supplier shares an address with an employee, a common indicator of an undisclosed related party or a fictitious vendor.

Requirements.

1. Use Power Query to load the Supplier and Employee Tables from CharlesRiver.xlsx into a new workbook, as queries named Supplier and Employee, both as Tables on new worksheets (Close & Load To, then Table and New worksheet, as in Tutorial 4.1, Step 7). Keep every column of Supplier, because Exercise 5.6 uses this Table too, and keep EmployeeID, EmployeeName, JobTitle, Address, and City of Employee.

2. On each Table, build a match key that joins the Address and City columns. Standardize the key with TRIM and UPPER so that differences in spacing and case do not hide a match, and explain why the key includes the city.

3. Use XLOOKUP or COUNTIFS to find the suppliers whose match key appears in the Employee Table, and bring the matching employee’s name and job title onto the Supplier Table.

4. Load the DisbursementPayment Table into the same workbook, and for each match, use SUMIFS on SupplierID to total the payments made to the supplier. State the range of payment dates the totals cover, because the extract runs past the fiscal year-end.

5. Prepare a brief memo (one half page) that lists the matches, the payments involved, and the procedures you would perform next.

Deliverable. A Supplier Table with the match key and the matched employee details, and a half-page memo.

Exercise 5.6: Reviewing the Supplier and Item Master Files

Table group: Procure-to-Pay, with the Item table from Master Data

Scenario. Before the audit team tests purchasing and sales, your senior asks you to review two master files. The supplier master file should yield a clean supplier list for the workpapers and be tested for duplicate suppliers and for suppliers that should not be receiving business. The item master file should be tested for items that are sold although their status says they should not be.

Requirements.

1. In the workbook of Exercise 5.5, using the Supplier Table, add a column, ProperName, that applies PROPER to SupplierName, and count the names that change. PROPER changes only the case of letters, which an ordinary comparison such as <> ignores, as the Watch out on logical tests explains, so compare the two columns with EXACT, which is case-sensitive: =NOT(EXACT([@SupplierName],[@ProperName])) is TRUE for a name that changes. Examine the changed names and explain why applying PROPER to this field would be a mistake.

2. Test for duplicate suppliers: count the suppliers that share a TaxID and the suppliers that share a BankAccount. The masked bank accounts begin with ****, which COUNTIF and COUNTIFS read as wildcards, so compare the values exactly, for example with =SUM(--(Supplier[BankAccount]=[@BankAccount])), which counts the suppliers with the same BankAccount as the row’s. Explain what a clean result shows and what it does not.

3. List the suppliers whose IsApproved flag is 0 and the suppliers whose SupplierRiskRating is High.

4. Load the PurchaseOrder Table into the same workbook, and use COUNTIFS on SupplierID with the PurchaseOrder and DisbursementPayment Tables to count the purchase orders and payments for each unapproved supplier.

5. In your copy of the analysis workbook, load the Item Table again as a new query named ItemMaster, with all its columns. Then test the item master against the invoice lines:

• list the items whose LifecycleStatus is Discontinued but whose IsActive flag is 1;
• on ItemMaster, count each item’s invoice lines with COUNTIFS on SalesInvoiceLine[ItemID], and list the items with IsActive 0 that were still invoiced, with the latest InvoiceDate of each (MAXIFS returns 0, which Excel shows as a meaningless date, for an item with no invoice lines, so show it only where the count is above zero);
• on the SalesInvoiceLine Table, look up each line’s LaunchDate from ItemMaster, flag the lines invoiced before their item’s launch, and count them by item.

6. Prepare a brief memo (one half page) that summarizes both reviews, recommends which suppliers the purchasing tests should focus on, and explains what each item finding means for the reliability of the item master.

Deliverable. A reviewed supplier list and item master with the test columns, and a half-page memo.

5.16 Further Reading

Dasu, T., & Johnson, T. (2003). Exploratory data mining and data cleaning. Wiley. https://doi.org/10.1002/0471448354 A book-length treatment of data quality, data cleaning, and the exploratory analysis that should precede any formal analysis. It is written for analysts who work with large, imperfect databases and is a good next step for readers who want to go beyond spreadsheet techniques.

Fernandes, A. A. A., Koehler, M., Konstantinou, N., Pankin, P., Paton, N. W., & Sakellariou, R. (2023). Data preparation: A technological perspective and review. SN Computer Science, 4(4), Article 425. https://doi.org/10.1007/s42979-023-01828-8 This open-access review describes the activities at the heart of data preparation, profiling, matching, mapping, format transformation, and data repair, and shows how they appear in programming, workflow, and table-based tools. It places the Excel and Power Query techniques of this chapter within the wider field.

Gantman, S., & Metzger, L. (2022). Vendor master data cleaning: A project for accounting class. Journal of Emerging Technologies in Accounting, 19(1), 165–171. https://doi.org/10.2308/jeta-2020-028 This teaching case sets a project to plan, carry out, and report on the cleaning of a large set of real vendor master data. It complements Exercise 5.6 by showing the planning, testing, and communication that a full data cleaning project requires.

Hunter, K., Alberti, C. T., Boss, S. R., & Thibodeau, J. C. (2020). IntelliClean: A teaching case designed to integrate data cleaning and spreadsheet skills into the audit curriculum. Journal of Emerging Technologies in Accounting, 17(2), 17–24. https://doi.org/10.2308/jeta-2020-025 Written for auditing courses, this case works through cleaning a data set in a spreadsheet and verifying its completeness and accuracy in accordance with auditing standards. It shows how the preparation checks in this chapter become audit evidence.

Lee, L. S., Casterella, G., & Wray, B. (2021). Preparing for audit data analytics with the AICPA general ledger audit data standards. Journal of Emerging Technologies in Accounting, 18(1), 131–157. https://doi.org/10.2308/jeta-2020-022 This exercise cleanses and transforms non-standardized client ledger data so that it conforms to the AICPA’s Audit Data Standards, and then tests it. It explains why standard data formats reduce the preparation burden on auditors.

Microsoft. (2026). Merge queries overview. Microsoft Learn. https://learn.microsoft.com/en-us/power-query/merge-queries-overview Microsoft’s reference for the Merge Queries command, which describes how to choose the key columns, how to expand the merged column, and each join kind, with links to worked examples of every kind. It also introduces fuzzy matching, which finds approximate matches between text columns.

Rahm, E., & Do, H. H. (2000). Data cleaning: Problems and current approaches. IEEE Data Engineering Bulletin, 23(4), 3–13. http://sites.computer.org/debull/A00dec/A00DEC-CD.pdf A widely cited classification of the data quality problems that data cleaning addresses, distinguishing problems within a single source from those that arise when sources are integrated. Its point that cleaning is most needed when combining heterogeneous sources applies directly to building a table such as InvoiceLines.

5.17 References

Fernandes, A. A. A., Koehler, M., Konstantinou, N., Pankin, P., Paton, N. W., & Sakellariou, R. (2023). Data preparation: A technological perspective and review. SN Computer Science, 4(4), Article 425. https://doi.org/10.1007/s42979-023-01828-8

Hunter, K., Alberti, C. T., Boss, S. R., & Thibodeau, J. C. (2020). IntelliClean: A teaching case designed to integrate data cleaning and spreadsheet skills into the audit curriculum. Journal of Emerging Technologies in Accounting, 17(2), 17–24. https://doi.org/10.2308/jeta-2020-025

Lee, L. S., Casterella, G., & Wray, B. (2021). Preparing for audit data analytics with the AICPA general ledger audit data standards. Journal of Emerging Technologies in Accounting, 18(1), 131–157. https://doi.org/10.2308/jeta-2020-022

Raschke, R. L., & Charron, K. F. (2021). Review of data analytic teaching cases, have we covered enough? Journal of Emerging Technologies in Accounting, 18(2), 247–255. https://doi.org/10.2308/jeta-2020-036