Chapter 7 · 45 minutes

Last updated November 11, 2026

Importing Data and Building a Reusable Excel Workflow

Opening scenario

Every month, the scheduling team sends you a fresh appointment export, along with two reference files that rarely change: a provider list and a clinic lookup. In Chapter 6 you built a scorecard from one clean file. Now comes the question that decides whether that scorecard survives past its first month: how does next month’s data get in?

The tempting path is to open each file, copy the rows together, delete the columns you do not want, and start calculating. That path works exactly once. It breaks the moment a new export arrives, and it leaves no record of how the final table was built, so no one, including you, can reproduce the result or explain it when a number is questioned. This chapter builds the professional alternative. You import each file without altering it, record every transformation as a repeatable step, and turn next month’s update into a single refresh.

By the end of this chapter, you will be able to:

  • Import CSV and text data into Excel without altering the raw source.
  • Use Power Query to build transformation steps that refresh when new data arrives.
  • Append files that share a structure and merge reference tables on a shared key.
  • Separate raw sources, working tables, and outputs within a documented workflow.
  • Lay out imported data in spreadsheet-ready form and document it with a codebook.
  • Set each column’s data type on import so identifier codes keep their leading zeros.
  • Document data sources and steps so an analysis can be repeated and defended.

Key concepts

If an analysis cannot be re-run when a new file arrives, it is not repeatable. It is a result produced once that cannot be defended. The work of this chapter is to replace a one-time cleanup task with a process that runs again on demand.

The chapter builds that process in three habits, each a section below:

  • Protect the source so the original data stays recoverable.
  • Record the steps so the transformation can be replayed on new data.
  • Document the result so the analysis can be reproduced and trusted.

Figure 1 previews the whole workflow these habits build.

Figure 1: The reusable workflow at a glance: raw source files stay untouched, Power Query records the transformation, the working table feeds the output, and a single refresh re-runs the whole chain when new data arrives. Source: concept schematic.

Protecting the source: raw, working, and output

The first rule of import is to never treat the file you received as your working table. Source files arrive in whatever shape the sending system produced, and once you edit one directly, you can no longer tell what was original and what you changed.

A reliable workbook separates data into three zones:

  • Raw data is the source, kept exactly as received and never edited by hand.
  • Working data is the transformed copy that your analysis is built on.
  • Output is the summaries, charts, and scorecards a reader sees.

Raw data is source data preserved exactly as received, so it stays recoverable and auditable. Working data is the cleaned, transformed copy that analysis is actually built on.

Keeping the three zones apart is what makes a result traceable. When a number is questioned, you can walk back from the output, through the working table, to the untouched source, and show every step in between. The raw, working, and output zones in Figure 1 make that chain visible.

Spreadsheet-ready layout and the codebook

Before you transform anything, decide how the imported table should be shaped. Spreadsheet-ready data, also called tidy data, follows a few plain rules (Wickham, 2014; Broman & Woo, 2018):

  • one row per unit of analysis,
  • one variable per column,
  • one value per cell,
  • consistent codes throughout.

A column of dates holds dates in one format, not 3/1, March 1, and last Monday mixed together. A yes-or-no field holds one pair of values everywhere, not Y, yes, and 1 in the same column. These rules may seem restrictive, but every later step, the filtering, grouping, joining, and summarizing, depends on them. Figure 2 shows the same records entered two ways.

Figure 2: The same appointment records entered two ways: a messy export on the left, and a spreadsheet-ready equivalent on the right with one row per appointment, one variable per column, one value per cell, and consistent codes. Source: concept schematic.

Spreadsheet-ready layout also depends on each column holding the appropriate data type, and the safest habit is to decide the type on import rather than after. Codes that look like numbers are a common source of error: a county FIPS or ZIP code such as 01029 loses its leading zero the moment it is read as a number, which breaks every later join without producing an error. Import identifier codes as Text, even when they contain only digits, and type date columns as Date so that 3/1 is never misread. Figure 3 shows the four cases you meet most often.

Figure 3: Choosing the appropriate data type on import: identifier codes are read as text so leading zeros survive, dates are read as dates, categories as text, and yes-or-no fields as a single 0 or 1. Source: concept schematic.

Decide the type on import, not after. An identifier read as a number drops its leading zeros (01029 becomes 1029), and a date read as text cannot be filtered or sorted by time. In Power Query, set code columns to Text and date columns to Date in the Changed Type step, and confirm the type icon on each column header before you continue.

A tidy table alone is an incomplete deliverable. The codebook completes it: also called a data dictionary, it records for each column its name, meaning, type, and allowed values.

A codebook (or data dictionary) records, for each column, its name, what it means, its data type, and its allowed values.

Chapter 5 treated the codebook as a governance requirement; here it is also a personal habit. Without one, sdoh_risk_score is an undefined column and a flag that reads 2 could mean anything. With one, a colleague, or you in six months, can use the data without guessing. Figure 4 shows a short codebook beside the table it documents.

Figure 4: A data table needs a codebook: for each column it records the name, meaning, data type, and allowed values, so a reader can use the data without guessing. Source: concept schematic.

Power Query records your steps

Power Query is Excel’s data-preparation layer (Escobar & Puls, 2021). You connect to a file, shape it through a graphical editor, and Power Query records each change as a step. When new data arrives, those steps replay in order.

A query is a recorded sequence of transformation steps that reads from a source and produces a table. Each step appears in the Applied Steps list, which you can review, reorder, or edit at any time.

Import is not a one-time event; it is a pipeline you can re-run. Figure 5 shows the Power Query Editor, where the Applied Steps list keeps a sequence like Source, Changed Type, Removed Other Columns, and Filtered Rows visible rather than hidden inside manual edits (Microsoft, 2026).

Figure 5: The Power Query Editor: the query list, the data preview, the data-type icon on each column, the Query Settings pane, and the Applied Steps list that records each transformation as a named step. Source: Microsoft Power Query screenshots, public sample data, not chapter records.

This walkthrough is written and tested for desktop Excel on Windows. Current Microsoft 365 versions of Excel for Mac (version 16.69 or later) support Power Query, but the entry point is Data > Get Data (Power Query) and a few load options differ. Power Query is not in Excel 2016 or Excel 2019 for Mac. Excel for the web can refresh an existing query, and some Microsoft 365 plans now allow limited Power Query editing in the browser, but browser support varies by plan, so follow along in desktop Excel for the full walkthrough.

Appending and merging: adding rows versus adding columns

Healthcare data rarely arrives as one complete file, so you combine files in two different ways. The difference matters, because they answer different questions and have different risks.

  • Append stacks the rows of files that share the same structure, such as twelve monthly exports with identical columns. Append adds rows.
  • Merge joins columns from a related table using a shared key, such as adding a provider’s type from a provider list. Merge adds columns.

Figure 6 contrasts the two. This is the point where Chapter 4’s lesson on keys and the unit of analysis becomes practical: a merge uses a key, and whether that key is unique decides what the merge does to your rows.

Figure 6: Append adds rows by stacking files that share the same columns; merge adds columns by joining a related table on a shared key. Source: concept schematic.

Verifying that a merge did not multiply rows

A merge can change the number of rows in a table without warning, and that is the risk to watch for.

  • If the key you join on is unique in the reference table, each row finds at most one match, and the row count does not change.
  • If the key is not unique, one row can match several, and expanding the merged column multiplies the rows. The summaries built on them are then incorrect.

A merge needs two checks, not one.

  • Cardinality: compare the row count before the merge and after you expand it. If the count grew, the key was not unique in the reference table, so expanding produced duplicates you did not intend.
  • Match completeness: confirm the expanded columns are not blank. Turn on Column quality and switch it to the entire data set, because it profiles only the first 1,000 rows by default (Microsoft, 2025). A left outer join keeps every original row even when the keys do not match, so an unchanged row count with empty provider_type or region values means the keys did not line up.

Because the editor’s preview counts only the first rows, read the true count with Transform > Count Rows, not the status bar.

Figure 7 shows the three outcomes a merge can produce: one match keeps the row count and fills the new column, no match keeps the count but leaves the column blank, and multiple matches inflate the count.

Figure 7: The three outcomes of a merge: a unique-key match preserves the row count and fills the new column, a non-match preserves the count but leaves the column blank, and a duplicated key multiplies rows. Always compare the count before and after. Source: concept schematic.

Walkthrough: build a refreshable appointment workflow

This build turns three separate files into one cleaned, merged table that refreshes on command. You import the monthly appointment export, add provider and clinic attributes from two reference tables, and summarize the result, all without editing a single source file. Figure 8 maps the path this build follows.

Figure 8: How three source files become one working table: one monthly appointment export and two reference tables, joined on shared keys, become a single cleaned and merged table. Source: concept schematic.

Before you start: put the three source files in a folder named raw and do not edit them. Open a new workbook and save it as ch07_workflow.xlsx. Every transformation happens inside the workbook’s queries, never in the source files.

Part A. Import the appointment export. Bring the file in through Power Query so the source stays untouched.

  1. On the Data tab, choose Get Data > From File > From Text/CSV (on a Mac, Data > Get Data (Power Query), then Text/CSV).
  2. Select raw/DS2_appointments_clean.csv and click Import.
  3. In the preview window, confirm that Delimiter reads Comma and the first row is treated as headers, then click Transform Data, not Load. The Power Query Editor opens.
  4. In the Query Settings pane on the right, confirm the Applied Steps list shows Source, Promoted Headers, and Changed Type (From Text/CSV adds Promoted Headers automatically when the first row holds the column names).
  5. In the Name box, type Appointments and press Enter.

Part B. Clean the query, then load it as a connection. Shape the table, verify its size, and set it aside for merging.

  1. On the Home tab, choose Choose Columns > Choose Columns. Check only appointment_id, patient_id, provider_id, facility_id, appointment_date, appointment_weekday, specialty_group, attendance_outcome, and no_show_flag, then click OK.
  2. Click the small type icon to the left of the appointment_date header and choose Date.
  3. Set no_show_flag to Whole Number, and confirm attendance_outcome and specialty_group are Text.
  4. Select specialty_group, then choose Transform > Format > Trim to remove any stray spaces.
  5. Click the dropdown arrow on attendance_outcome, uncheck everything except Completed and No Show, and click OK.
  6. Read the true row count. The status bar counts only the preview, so choose Transform > Count Rows instead: the table becomes a single value, 30571. Note it as your baseline, then delete the Counted Rows step from Applied Steps so the table returns.
  7. On the Home tab, choose Close & Load > Close & Load To, select Only Create Connection, and click OK. You return to Excel, and Appointments appears in the Queries & Connections pane.
Figure 9: Connection only or worksheet Table? A connection-only query is available for merging but is not written to a sheet; a worksheet Table is loaded so you can read and chart it. Source: concept schematic.

Part C. Import the two reference tables as connections. You do not need these on a sheet, only available to merge (Figure 9).

  1. Choose Data > Get Data > From File > From Text/CSV, select raw/DS1_provider_dim.csv, and click Transform Data. Rename the query Providers, and use Choose Columns to keep provider_id and provider_type.
  2. On the Home tab, choose Close & Load > Close & Load To, select Only Create Connection, and click OK.
  3. Repeat steps 13 and 14 for raw/DS1_facility_dim.csv, naming the query Facilities and keeping facility_id, facility_type, and region.

Part D. Merge the provider list. Add each appointment’s provider type.

  1. In the Queries & Connections pane, double-click Appointments to reopen the editor.
  2. On the Home tab, choose Merge Queries.
  3. Click the provider_id column in Appointments, choose Providers from the lower dropdown, and click its provider_id column.
  4. Set Join Kind to Left Outer and click OK. A new column appears at the right.
  5. Click the expand icon (the two arrows) on the new column’s header, uncheck Use original column name as prefix, keep only provider_type, and click OK.

Part E. Merge the clinic list. Add each appointment’s facility type and region.

  1. Choose Home > Merge Queries again, match facility_id to Facilities[facility_id], set Left Outer, and click OK.
  2. Expand the new column, keep facility_type and region, and click OK.

Part F. Verify the merge two ways. Confirm it added columns, not rows, and that every key matched.

  1. Cardinality. Choose Transform > Count Rows; the result is 30571, the same as your baseline, so expanding the merges added columns, not rows. Delete the Counted Rows step to restore the table. Had the count grown, a reference key would not have been unique.
  2. Match completeness. On the View tab, check Column quality. By default it profiles only the first 1,000 rows, so click the message in the lower-left corner of the window and switch it to Column profiling based on entire data set. Confirm provider_type, facility_type, and region each read 100% valid with 0% empty. Empty values would mean the keys did not match, even though the row count did not change.

Part G. Load the working table. Send the cleaned, merged query to a sheet.

  1. On the Home tab, choose Close & Load. Because Appointments is currently connection-only, Close & Load To may be unavailable from the editor, so set the destination from Excel instead: in the Queries & Connections pane, right-click Appointments, choose Load To, select Table and New worksheet, and click OK. Double-click the sheet tab and rename it Working.
  2. Confirm the result on the sheet with a formula: =ROWS(Appointments[appointment_id]) returns 30571, and =COUNTBLANK(Appointments[region]) returns 0. (Appointments is the name Excel gives the loaded Table.)
Figure 10: Why the average of a 0/1 flag is a rate: averaging a no-show flag made of zeros and ones returns the share of ones, which is the no-show rate. Source: concept schematic.
Figure 11: Show volume and rate separately: appointment counts (Central 10,584, West 4,585) and the no-show rate (17.0 and 14.6 percent) need separate scales, because a shared count axis would flatten the rate. Source: DS2 appointments (completed-or-no-show).

Part H. Build and chart a summary. Reuse the PivotTable skill from Chapters 3 and 4 on the merged table; the average of the 0/1 no-show flag is the no-show rate (Figure 10). Because counts dwarf a rate near 0.16, chart the volume and the rate on separate scales (Figure 11).

  1. Click any cell in the Working table and choose Insert > PivotTable. Place it on a new sheet named Output.
  2. Drag region to Rows.
  3. Drag appointment_id to Values; it shows as Count of appointment_id.
  4. Drag no_show_flag to Values as well, click it, choose Value Field Settings > Average, and format it as a percentage with one decimal.
  5. Read the result. The five regions read Central 10,584, East 6,849, South 5,358, West 4,585, and North 3,195, and they sum to 30,571. The no-show rate is highest in Central, about 17.0%, and lowest in West, about 14.6%.
  6. Chart the rate on its own. Because the counts run to the thousands and the rate sits near 0.16, one chart of both would make the rate unreadable. Add a second PivotTable on Output with region on Rows and only the Average of no_show_flag in Values, formatted as a percentage.
  7. Select the second PivotTable and choose PivotTable Analyze > PivotChart > Clustered Bar, titled No-show rate by region. On the first PivotTable, apply Home > Conditional Formatting > Data Bars to the count column so the volumes stay visible.
Figure 12: Edit the query, not the loaded cell: a correction recorded as a query step survives a refresh, while a value typed into the loaded table is overwritten the next time the query runs. Source: concept schematic.

Part I. Refresh without overwriting the source. Keep every export recoverable, and make every correction in the query rather than in the loaded table (Figure 12).

  1. Never overwrite a file in raw, and decide which kind of update you have. If you are replacing the current two-year extract, save the new one under a dated name, such as raw/DS2_appointments_2026-01.csv, and edit the Source step of the Appointments query to point at it.
  2. If instead you receive one new month at a time, do not repoint the Source step. This query reads the cumulative two-year file, so pointing it at a folder of only the new months would drop that history. Add each new month with the Append path in Part J instead, stacking it onto the existing data. The From Folder option (Windows) fits a different setup: a query built from the start on a raw/appointments_monthly/ folder that holds the complete history as one file per month. In that setup you drop each new month into the folder and refresh, but you never also keep the cumulative two-year file in the folder, or the overlapping rows will duplicate.
  3. After either update, choose Data > Refresh All; every step, the working table, and the PivotTable re-run. On a Log sheet, record the source file names, the extract date, the coverage window, the join keys (provider_id and facility_id), the baseline and post-merge row counts, and the filter to completed and no-show appointments. Add a short codebook for the columns you kept, then save.
Figure 13: Which kind of update did you receive? A full replacement extract means repointing the query’s Source step; a new monthly file means adding it to the monthly folder and refreshing. Source: concept schematic.

Part J. Append monthly files. Stack several months into one table. Before you do, decide which kind of update you received (Figure 13): a full replacement means repointing the Source step, while a new monthly increment is appended onto the existing data, as you do here.

  1. Create a folder raw/appointments_monthly/ and place DS2_appointments_2025-11.csv and DS2_appointments_2025-12.csv (supplied with the datasets) inside it, with no other files.
  2. Import each file with From Text/CSV as its own query, named Nov and Dec, giving both the same columns and types.
  3. Select the Nov query, then choose Home > Append Queries > Append Queries as New and add Dec. A new query stacks the two, November’s rows followed by December’s.
  4. Read the count with Transform > Count Rows: 3056, the sum of 1437 November and 1619 December rows. These are the raw monthly exports, so 3056 counts all attendance outcomes, unlike the walkthrough’s 30571, which was filtered to completed and no-show appointments. On Windows you can instead point From Folder at raw/appointments_monthly/ to append every month at once; Append Queries is the path that also works on a Mac.

Every column on the working table traces back to an untouched, dated source file, and nothing was edited by hand. A row count only means something alongside its population (Figure 14); overlapping extracts would double count if the cumulative and monthly folders were mixed (Figure 15); and the workflow log records the chain so the result can be reproduced and defended (Figure 16). When next month’s export arrives, you add it and refresh, and the whole chain re-runs the same way.

Figure 14: A row count only means something with its population: the walkthrough’s 30,571 counts completed-or-no-show appointments, while the raw monthly append of 3,056 counts every outcome. Source: DS2 appointments and the monthly extracts.
Figure 15: Why overlapping extracts double count: mixing a cumulative two-year file with monthly files that repeat the same rows makes the append count those appointments twice. Source: concept schematic.
Figure 16: The workflow log makes a result traceable: it records the source files, extract date, coverage window, join keys, row counts, and filters, so the analysis can be reproduced and defended. Source: concept schematic.

Practice on your own

These two exercises reuse the walkthrough’s techniques on new questions. The first is fully guided, like the walkthrough, so you have a second worked example to follow. The second fades the support: it names the step and the files and gives you a value to check against, but leaves you to assemble the query yourself, which is the real skill. Try each block before checking the answer key.

Practice A: a provider-attendance workflow

Build a complete workflow, from import to chart, that reports the no-show rate by provider type and by specialty. This exercise is fully guided, like the walkthrough.

  1. Open a new workbook and save it as ch07_providers.xlsx.
  2. Choose Data > Get Data > From File > From Text/CSV and select raw/DS2_appointments_clean.csv.
  3. In the preview, click Transform Data to open the editor.
  4. In the Name box, type Appointments.
  5. Choose Home > Choose Columns, keep appointment_id, provider_id, specialty_group, attendance_outcome, and no_show_flag, and click OK.
  6. Set no_show_flag to Whole Number and confirm attendance_outcome is Text.
  7. Filter attendance_outcome to Completed and No Show.
  8. Confirm the total with Transform > Count Rows (30571), then delete the Counted Rows step so the table returns.
  9. Choose Home > Close & Load > Close & Load To > Only Create Connection to set Appointments aside and return to Excel.
  10. Choose Data > Get Data > From File > From Text/CSV, select raw/DS1_provider_dim.csv, and click Transform Data. Rename it Providers.
  11. In Providers, use Choose Columns to keep provider_id and provider_type, then choose Home > Close & Load > Close & Load To > Only Create Connection.
  12. Reopen the Appointments query from the Queries & Connections pane.
  13. Choose Home > Merge Queries, match provider_id to Providers[provider_id], set Left Outer, and click OK.
  14. Expand the new column, uncheck the prefix option, and keep provider_type.
  15. Verify: Transform > Count Rows returns 30571, then delete the Counted Rows step. On the View tab, turn on Column quality, switch it to based on entire data set, and confirm provider_type shows 0% empty.
  16. Choose Home > Close & Load. If Close & Load To is unavailable because the query is connection-only, right-click Appointments in the Queries & Connections pane and choose Load To > Table > New worksheet. Rename the sheet Working.
  17. Click any cell in the Working table and choose Insert > PivotTable on a new sheet named Output.
  18. Drag provider_type to Rows, appointment_id to Values (Count), and no_show_flag to Values as well, set to Average and formatted as a percentage.
  19. Right-click a value in the count column and choose Sort > Sort Largest to Smallest.
  20. Apply Home > Conditional Formatting > Data Bars to the count column.
  21. Chart the rate on its own: add a second PivotTable with provider_type on Rows and only the Average of no_show_flag in Values, then select it and choose PivotTable Analyze > PivotChart > Clustered Bar, titled No-show rate by provider type.
  22. Add a third PivotTable from the Working table on the Output sheet.
  23. Put specialty_group on Rows, with a Count of appointment_id and an Average of no_show_flag formatted as a percentage.
  24. Apply All Borders and a light header fill to the PivotTables.
  25. Add a Log sheet recording the two source files, the extract date, the join key (provider_id), and the filter assumption.
  26. Save the workbook.

Expected result: the provider-type table shows six types, NP 10,909, MD 10,017, PA 3,363, RN 3,353, DO 1,826, and Behavioral health clinician 1,103, summing to 30,571. Among those types, the highest no-show rate is Behavioral health clinician at about 17.8% and the lowest is PA at about 14.5%. The specialty table shows eight groups, led by Primary care 20,257 and trailed by OB/GYN 558, also summing to 30,571; its highest no-show rate is Behavioral health at about 18.5% and its lowest is Urgent care at about 11.2%. No row is blank, because every key matched. Check your workflow against Figure 17.

Figure 17: Practice A checkpoint: provider-type and specialty counts and no-show rates that reconcile to 30,571 after the merge, with no blank keys. Source: DS2 appointments joined to DS1 provider_dim.

Practice B: an emergency-department workflow

Demonstrate that the same import-and-merge procedures work on a different dataset. Build an ED workflow that reports visit volume and the left-without-being-seen rate by payer group. This exercise names each step and gives you a value to check against, but leaves the specific choices to you.

  1. Create a new workbook and save it as ch07_ed.xlsx. Put raw/DS3_ed_throughput_clean.csv and raw/DS1_payer_dim.csv in your raw folder.
  2. Import the ED file with Power Query, opening the editor rather than loading straight to a sheet. (Which button opens the editor?)
  3. Rename the query ED.
  4. Keep only the columns this question needs: the encounter id, payer_id, and left_without_being_seen_flag. (Use Choose Columns.)
  5. Set left_without_being_seen_flag to Whole Number.
  6. Read the true count with Transform > Count Rows and note it, then delete the Counted Rows step. (It should be 18,000.)
  7. Set ED aside as a connection and return to Excel. (Which Close & Load option creates a connection only?)
  8. Import the payer file as a separate query, opening the editor. (Which button opens the file in the Power Query Editor rather than loading it straight to a worksheet?)
  9. Rename it Payers.
  10. In Payers, keep only payer_id and payer_group, then load it as a connection only.
  11. Reopen the ED query to begin the merge.
  12. Start a merge from the Home tab. (Which command combines columns from another query?)
  13. Choose the column the two tables share as the match. (Which column appears in both ED and Payers?)
  14. Set the join kind so that every ED visit is kept. (Which join kind keeps all of the left table’s rows?)
  15. Confirm the merge. A new column appears at the right.
  16. Expand the new column and keep payer_group only, unchecking the name-prefix option.
  17. Verify cardinality with Transform > Count Rows, compared with step 6, then delete the Counted Rows step. (Still 18,000? If it grew, the key was not unique.)
  18. Verify match completeness: on the View tab, turn on Column quality, switch it to based on entire data set, and confirm payer_group shows 0% empty.
  19. Load the merged ED query to a Table on a New worksheet, and name the sheet Working. If Close & Load To is greyed out because the query is connection-only, use Close & Load, then right-click ED in Queries & Connections and choose Load To > Table > New worksheet.
  20. Insert a PivotTable on a new sheet named Output.
  21. Put payer_group on Rows.
  22. Add a Count of the encounter id to Values.
  23. Add left_without_being_seen_flag to Values as an Average, formatted as a percentage. (This is the left-without-being-seen rate; the flag is never blank, so it covers all visits.)
  24. Sort the table by visit count, largest to smallest, and add data bars to the rate column.
  25. Chart the rate on its own with a Clustered Bar PivotChart built from a second, rate-only PivotTable.
  26. Add a Log sheet recording the two source files, the extract date, the join key, and the columns you kept, then save.

Check your work: the ED file holds 18,000 visits, and the merge keeps it at 18,000. The six payer groups, Commercial 7,334, Medicare 4,527, Medicaid 2,406, Exchange 1,735, Self-pay 1,474, and Other 524, sum to 18,000. The left-without-being-seen rate is 3.2% overall, ranging from 2.8% (Self-pay) to 3.6% (Exchange). If the total grew after the merge, the payer_id values in Payers are not unique, so check Payers for duplicate keys. Confirm your result against Figure 18.

Figure 18: Practice B checkpoint: the ED workflow keeps 18,000 visits after the payer merge, with a left-without-being-seen rate of 3.2 percent overall. Source: DS3 ED throughput joined to DS1 payer_dim.

Going deeper

A merge multiplies rows only when the reference key is not unique, so the safeguard is to know the grain of the table being joined. A provider list has one row per provider and a clinic list one row per clinic, so joining appointments to either adds columns without adding rows (Figure 19). A claim-line file, by contrast, has many rows per claim, so joining a claim header to it will multiply. When the same query must be written repeatedly, or reference tables outgrow a worksheet, that indicates the data should move into a database and a tool designed for joins should perform the work. Power Query is an entry point to that approach, not its final form.

Figure 19: The grain of a table predicts what a merge will do: joining to a one-row-per-provider or one-row-per-clinic table adds columns, while joining to a many-rows-per-claim table multiplies rows. Source: concept schematic.

Real-world spotlight

The following is an illustrative scenario, not a specific organization. An analyst was asked to reproduce last quarter’s utilization number and could not. The original had been built by hand: files pasted together, columns deleted, totals typed in. Nothing was written down, and the source files had since been overwritten. The team rebuilt the analysis as a refreshable Power Query workflow, and the next time the question came back, the answer took minutes instead of days. The improvement came not from greater skill but from a version that could be re-run and explained.

  • Keep raw source files exactly as received; do the work downstream in a query so the source stays recoverable.
  • Lay data out spreadsheet-ready, one row per unit and one variable per column, and record a codebook for each column.
  • Set each column’s data type on import, reading identifier codes as text so leading zeros survive.
  • Power Query turns import into a pipeline of visible, refreshable steps rather than a one-time edit.
  • Append adds rows from files with the same structure; merge adds columns from a related table using a shared key.
  • Check a merge two ways: did the row count grow (a duplicated key), and are any expanded columns blank (keys that did not match)?
  • Document sources, extraction dates, coverage, and assumptions, because a healthcare analysis is a chain of evidence, not a single number.
  1. Explain the difference between an append and a merge, using the appointment, provider, and facility files as your example.
  2. A new month’s appointment export arrives. List the steps that add it to your analysis without rebuilding the workbook.
  3. Name four items a workflow log should record, and explain why each matters if the result is later challenged.
  4. After a merge, your row count rose from 30,571 to roughly 61,000. State the most likely cause and one way to confirm it.
  5. Explain why you should edit the query, not the loaded table, when a transformation needs to change.
  6. You receive a provider list with two rows for one provider. Explain what will happen if you merge it to the appointments, and how you would catch it.
  7. A ZIP code column imports as a number, turning 02139 into 2139. Explain what went wrong and how you would prevent it when importing.
  1. An append stacks rows from files with the same columns, such as twelve monthly appointment exports; a merge adds columns from a related table through a shared key, such as attaching provider_type and region to each appointment. Append grows the table downward; merge grows it sideways.

  2. If the new file is a single month, add it with the Append path (Part J): stack the new month’s query onto the existing data and choose Data > Refresh All, so the recorded steps re-run without rebuilding the workbook. Do not repoint the Source step for an increment, because this query reads the cumulative two-year file and repointing it would drop that history; repoint only for a full replacement extract, such as a corrected two-year export. (A query built from a monthly folder is the exception: there you drop the new file into the folder and refresh.) Either way, every earlier source stays recoverable.

  3. A workflow log should record the source file names, the extract date, the coverage window, and the join keys and assumptions. Each matters because, if the result is challenged, you can show exactly which files produced it, for what period, and how they were combined, so the analysis can be reproduced and defended.

  4. The join key was not unique in the reference table, so each appointment matched several reference rows, and expanding the merged column multiplied the table. Confirm it by reading Transform > Count Rows before and after, or by checking the reference table for duplicate keys. A different fault, an unchanged count with blank expanded columns, points instead to keys that did not match at all.

  5. The loaded table is an output; the query is the recorded recipe. Editing the query keeps the step list accurate and refreshable, while editing the output by hand breaks reproducibility, because the change is lost the next time the query refreshes.

  6. The appointments that use that provider will each match both rows, so those appointments double in the result and every count built on them is inflated. You would catch it by comparing the row count before and after the merge; a count that grew signals a duplicated key.

  7. The column was read as a number, which drops leading zeros, so 02139 became 2139. A ZIP code is a label, not a quantity, so import identifier codes as Text by setting the type in the Changed Type step, and confirm the type icon before you build on the column.

  • Broman, K. W., & Woo, K. H. (2018). Data organization in spreadsheets. The American Statistician, 72(1), 2-10. https://doi.org/10.1080/00031305.2017.1375989
  • Escobar, M., & Puls, K. (2021). Master your data with Power Query in Excel and Power BI: Leveraging Power Query to get and transform your task flow. Holy Macro! Books.
  • Microsoft. (2025). Using the data profiling tools. Microsoft Learn. https://learn.microsoft.com/power-query/data-profiling-tools
  • Microsoft. (2026). About Power Query in Excel. Microsoft Support. https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a
  • Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1-23. https://doi.org/10.18637/jss.v059.i10
Raw data
Source data kept exactly as received, so it stays recoverable and auditable.
Working data
The transformed copy of the data that analysis is built on.
Output
The summaries, charts, and scorecards a reader sees, built from the working data.
Spreadsheet-ready data
Data laid out tidily, with one row per unit, one variable per column, one value per cell, and consistent codes.
Codebook
A record of each column’s name, meaning, type, and allowed values; also called a data dictionary.
Power Query
Excel’s data-preparation layer, which records refreshable transformation steps.
Query
A recorded sequence of steps that reads from a source and produces a table.
Applied Steps
The list in the Power Query Editor that shows each recorded transformation in order.
Append
Combining files with the same structure by stacking their rows.
Merge
Joining columns from a related table to a main table using a shared key.
Join key
The column shared by two tables that a merge matches on, such as provider_id.
Left outer join
A merge that keeps every row of the main table and attaches matching columns from the reference table.
Refresh
Re-running a query’s recorded steps so the output reflects updated source data.
Connection-only query
A query that is loaded for use in merges but not written to a worksheet.
Workflow log
A short record of source files, dates, keys, and assumptions that makes an analysis reproducible.
Cite this chapter
@incollection{sher2026ch07,
  author = {Sher, Hoshiar J.},
  title = {Importing Data and Building a Reusable Excel Workflow},
  booktitle = {Introduction to Healthcare Data Analytics Using Excel},
  year = {2026},
  doi = {10.5281/zenodo.23327956},
  url = {https://yar177.github.io/intro-to-HDA-site/ch07.html}
}