2.3 Import and clean data with Power Query

Build and test the live patient-data workbook

A data pipeline is a repeatable sequence that imports, cleans, combines, checks, and reports data. Power Query records these operations as named steps. When the source changes, refresh repeats the steps on the new data.

Each reporting round supplies consultation records in CSV files and patient attributes in JSON files. Building the preparation steps once allows the same workbook to process the next round without copying and pasting the records by hand.

Use a separate workbook for this exercise. Save a blank workbook as Module_2_Excel/workbooks/Patients_live_refresh.xlsx. This chapter calls it the live patient-data workbook. Keep its CSV files, JSON files, queries, and worksheets separate from the surgery workbook used in Excel basics and data tables and Formulas, functions, and lookups. The small supplied dataset lets you check every changed result by hand.

Why use a repeatable import

Manual cleaning is difficult to repeat and easy to perform differently the next time. Power Query keeps the source path, type changes, cleaning decisions, and merge steps together. A refresh can then repeat the same procedure, while control totals show whether the new result is complete.

This chapter follows one complete import-and-refresh cycle.

In this chapter, you will:

compare how patient records are represented in Excel, CSV, TSV, and JSON;
preserve received files and place working copies at stable source paths;
import, type, profile, and clean the consultation and patient data;
check identifiers before merging and detect unmatched or multiplied rows;
load the required outputs while retaining intermediate query checks; and
replace round 1 with round 2, refresh the workbook, and diagnose common faults.

Figure 1 shows the current Windows Data tab. The same tab holds Get Data, refresh controls, data types, Data from Picture, Analyze Data, and What-If Analysis; this chapter uses only the import and refresh controls.

Current Excel Data tab showing Get Data, From Text/CSV, From Picture, Refresh All, and Analyze Data — Figure 1: Current Data Ribbon with Get Data, From Text/CSV, From Picture, Refresh All, and Analyze Data visible.

2.3.1 Identify what data are available

An import begins with a source and a structure. Common sources include:

Use Table 1 to decide what to inspect for the source format you receive.

Table 1: Common spreadsheet data sources and their import questions.

Source	Typical structure	What to inspect before importing
Excel workbook	Worksheets, Tables, and named ranges	Which object contains the records; formulas versus stored values
CSV or TSV	One flat table separated by commas, semicolons, or tabs	Delimiter, encoding, header row, decimal convention, and data types
JSON	Records that may contain nested lists or objects	Which level represents one record and which fields must be expanded
Text or log file	Lines with fixed-width or delimited fields	Record boundaries, separators, timestamps, and malformed lines
Folder	Several files with a common schema	File naming, repeated headers, schema changes, and duplicate periods
Database	Related tables accessed through a connector	Table grain, keys, permissions, and query limits
Web page or API	Tables or structured responses retrieved from a URL	Stability, authentication, pagination, update frequency, and usage limits

A field is one named item, such as region. A record contains the fields for one patient. A header names the fields at the start of a table or text file. The same 2 patient-update records appear in 4 formats below. Compare the Excel worksheet in Table 2, the CSV text in Listing 1, the TSV text in Listing 2, and the JSON array in Listing 3.

An XLSX file is a workbook package. It can contain several worksheets, formulas, formats, and other workbook objects. You normally inspect an XLSX file in Excel. The worksheet in Table 2 displays the records in cells, with the field names in the header row.

Table 2: Two patient-update records displayed as an Excel worksheet table.

`patient_id`	`region`	`risk_level`	`contact_preference`
`P001`	`East`	`Standard`	`Email`
`P002`	`North`	`Standard`	`Phone`

A delimiter is the character that separates one field from the next. CSV usually uses a comma. In Listing 1, the first line is the header and the next 2 lines are records.

Listing 1: Two patient-update records stored as comma-separated values.

patient_id,region,risk_level,contact_preference
P001,East,Standard,Email
P002,North,Standard,Phone

TSV uses a tab as its delimiter. The spaces between fields in Listing 2 are tab characters, even though a text editor may display them as wide spaces.

Listing 2: Two patient-update records stored as tab-separated values.

patient_id  region  risk_level  contact_preference
P001    East    Standard    Email
P002    North   Standard    Phone

JSON names every field inside each record. Curly brackets enclose one record, and square brackets enclose the array of records in Listing 3. The field names repeat because each JSON object describes its own record.

Listing 3: Two patient-update records stored as a JSON array.

[
  {
    "patient_id": "P001",
    "region": "East",
    "risk_level": "Standard",
    "contact_preference": "Email"
  },
  {
    "patient_id": "P002",
    "region": "North",
    "risk_level": "Standard",
    "contact_preference": "Phone"
  }
]

In the worksheet, CSV, and TSV examples, one row is one record. JSON uses one object between curly brackets for each record. All 4 fields in this patient update are text. Assign the Text data type during import after confirming each field’s meaning and received values.

The format comparison uses identical patient-update records so that you can compare their structure. The exercise files divide the data by purpose. The CSV contains consultation records, while the JSON contains patient attributes that will be joined to those consultations through patient_id.

A CSV file is flat: every record uses the same columns. JSON is semi-structured: a record can contain another object or a list. A PDF or image may contain visible data without providing reliable table structure. Prefer the most structured authoritative source available.

Preview large files before loading

An Excel worksheet has 1,048,576 rows and 16,384 columns. A CSV can exceed those limits even though Excel can connect to it. For a large file, inspect its headers, delimiter, types, and a small sample in Power Query before loading. Load only the required fields and rows to a worksheet, or use the Data Model when the complete result is too large for the grid.

Before importing, record:

who or what produced the data;
what one row or record represents;
which field identifies a record;
when the source was last updated;
whether the file replaces an earlier snapshot or contains only new records; and
what changes are expected between refreshes.

2.3.2 Organize received and derived files

Keep received data separate from working inputs and results. This folder arrangement adapts the raw/processed distinction in Cookiecutter Data Science to a small spreadsheet project.

Create the folders in Listing 4 so that received files, working inputs, and derived results remain separate.

Listing 4: Folder arrangement for the Excel project.

Module_2_Excel/
  data/
    raw/round-1/       received files, preserved unchanged
    raw/round-2/       the next received files, preserved unchanged
    current/           working copies at stable query paths
    processed/         derived CSV exports, when needed
  workbooks/           working Excel files and their queries
  reports/             delivered report snapshots

Intermediate query results can stay in Power Query; exporting a file after every step is unnecessary. Create a control worksheet with the source round, filenames, update procedure, row counts, and checks. Record corrections with the received value, action, and reason. Never edit a query’s loaded output as the permanent correction, because the next refresh may overwrite it.

Follow the sequence in Figure 2 for each source round. Check the combined data before summarizing it, then repeat the checks after refresh.

Figure 2: Receive, inspect, import, clean, combine, check, summarize, and refresh the data.

2.3.3 Inspect the two source rounds

Download all four files into the corresponding data/raw/round-1 and data/raw/round-2 folders:

Each CSV row represents one consultation, identified by consultation_id. Each JSON record describes one patient, identified by patient_id. Several consultations may belong to one patient. The JSON is an array of records:

Inspect the brackets, field names, and values in Listing 5 to recognize a JSON array containing one patient record.

Listing 5: One patient record inside a JSON array.

[{"patient_id":"P001","region":"East","risk_level":"Standard","contact_preference":"Email"}]

Both round-2 files are full replacements. They include the records still present from round 1, additional records, and a changed risk category for P002. Appending a full replacement to the previous round would duplicate records. An incremental delivery, by comparison, contains only changes or new records and needs an explicit rule for applying them.

Complete round-1 source records

The complete consultation CSV contains these six records.

Compare the imported consultation preview with all six received records in Table 3.

Table 3: All six consultation records in round 1.

`consultation_id`	`patient_id`	`consultation_date`	`procedure`	planned duration	actual duration	`status`
`C001`	`P001`	`2026-09-28`	`Intake`	30	35	`Completed`
`C002`	`P002`	`2026-09-29`	`Follow-up`	45	50	`Completed`
`C003`	`P003`	`2026-09-30`	`Intake`	40	40	`Completed`
`C004`	`P001`	`2026-10-01`	`Procedure A`	60	65	`Completed`
`C005`	`P004`	`2026-10-02`	`Follow-up`	30	25	`Completed`
`C006`	`P002`	`2026-10-03`	`Procedure B`	50		`Cancelled`

The JSON update file contains four records:

Check that the JSON import contains the four patient records in Listing 6.

Listing 6: Complete round-1 patient-update JSON

[
  {"patient_id":"P001","region":"East","risk_level":"Standard","contact_preference":"Email"},
  {"patient_id":"P002","region":"North","risk_level":"Standard","contact_preference":"Phone"},
  {"patient_id":"P003","region":"West","risk_level":"Review","contact_preference":"Email"},
  {"patient_id":"P004","region":"South","risk_level":"Standard","contact_preference":"Portal"}
]

The CSV has one row per consultation. The JSON has one row per patient update. The shared key is patient_id. Merging the update fields into the consultations must leave six consultation rows in round 1.

2.3.4 Distinguish a snapshot from a connection

Opening a CSV directly creates a snapshot. The values are visible, but the workbook does not retain a reusable import procedure.

A Power Query connection stores a sequence of steps:

connect to the source;
identify the records and headers;
assign data types;
filter, rename, split, or combine fields;
load the result to a Table or the Data Model; and
repeat those steps when the source changes.

The connection is refreshable, not automatically trustworthy. A refresh can still load a duplicated file, a changed schema, or an incomplete period.

Establish the CSV baseline

Official Microsoft tutorials

Use Microsoft’s Import data from data sources with Power Query tutorial to locate the connectors and Power Query Editor. The Merge queries and join tables and Add data and then refresh your query tutorials include official videos and written steps for the later stages. Microsoft’s Profile data to view statistics tutorial explains the column-quality, distribution, and profile views used in this chapter.

For the patient exercise, load the worksheet Tables specified below before building the PivotTables. This lets you inspect the records and control formulas first.

Copy consultations_round_1.csv into data/current and rename the copy consultations.csv. Copy patient_updates_round_1.json into the same folder and rename the copy patient_updates.json. Keep the downloaded round files in data/raw/round-1 unchanged.
In Patients_live_refresh.xlsx, select Data > Get Data > From File > From Text/CSV and choose consultations.csv. If a connector search opens, search for Text/CSV. On macOS, use Data > Get Data > Text/CSV.
In the preview, confirm the comma delimiter and the seven field headings. Compare the six records with Table 3. Select Transform Data to open Power Query Editor.

If q_consultations already exists, open Data > Queries & Connections, right-click that query, and select Edit. Continue with its existing Source and Changed Type steps; keep one CSV query for this file.

2.3.5 Import CSV and JSON

Inspect the editor and assign CSV types

The Queries pane on the left lists the queries in this workbook. The center contains a preview of the selected query. Query Settings on the right contains its name and Applied Steps. Each step records an operation that will run again during refresh. If a pane is hidden, enable it on the View tab.

In Query Settings > Properties > Name, enter q_consultations. Select the last Applied Step before adding an operation. Selecting an earlier step shows an earlier state; adding a step there inserts it into the middle of the procedure.
Select the type icon to the left of the consultation_id heading and choose Text. Set patient_id, procedure, and status to Text as well. Set consultation_date to Date, and planned_duration_min and actual_duration_min to Whole Number. If Excel asks whether to replace the existing type conversion, choose Replace current.
Inspect the preview after conversion. It should still contain six records. C006 is cancelled and has a blank actual duration; keep that blank. An Error in a date or number needs investigation before loading. For ambiguous date or decimal conventions, right-click the heading and use Change Type > Using Locale with the convention used by the source.
Select Home > Close & Load > Close & Load To…. Choose Table and New worksheet, then OK. Select a cell in the loaded result and use Table Design > Table Name to name the Table ConsultationsLive. The query is q_consultations; its worksheet Table is ConsultationsLive.

Use the Applied Steps sequence shown in Microsoft’s Power Query import tutorial. Select each step in q_consultations and observe how the preview changes. Use a step’s gear button to reopen its settings, then reopen the loaded query from Excel to confirm that the procedure remains editable.

Expand and load the patient JSON

Return to Excel and select Data > Get Data > From File > From JSON. On macOS, select the JSON connector under Get Data. Open the working copy data/current/patient_updates.json.
Inspect the first preview. A List contains the four patient Records. If that List is shown, select To Table, accept the default delimiter and extra-column settings, and choose OK. The preview now has a Column1 column with Record in each row.
Select the expand icon with two arrows in the Column1 heading. Select patient_id, region, risk_level, and contact_preference. Clear Use original column name as prefix, then choose OK. Some Excel versions already expand this JSON into a table. If the four named columns are present, inspect them and continue without another expansion.
Set all four columns to Text and compare their values with
1. Confirm four records and four columns. Name the query q_patient_updates in Query Settings.
Select Home > Close & Load > Close & Load To… > Table > New worksheet, then OK. Select a cell in the output and use Table Design > Table Name to name it PatientUpdates.

The Windows and macOS connector interfaces differ. Check Microsoft’s Power Query source matrix if a command is unavailable. Use the supported desktop application for this local-file exercise.

If a field contains a nested object or list, decide whether it belongs in the current table or in a separate related table. Do not flatten every nested value merely because Power Query can expand it.

2.3.6 Profile the complete query

Power Query profiles the first 1,000 rows by default. Switch the setting to the complete dataset before using the profile as evidence. Microsoft describes the controls and their scope in Profile data to view statistics in Power Query.

Reopen q_consultations in Power Query Editor and select View.
Enable Column quality, Column distribution, and Column profile.
In the lower-left corner, change Column profiling based on top 1000 rows to Column profiling based on entire data set. The profile now describes the entire dataset instead of only its first 1,000 rows.
Select each field. Column quality reports valid, error, and empty values. Column distribution reports distinct values and their frequencies. Column profile reports statistics for the selected field.
Confirm 6 consultation records, no conversion errors, and 1 empty actual_duration_min value. The empty value belongs to cancelled record C006. Record the result on control.
Repeat the profile for q_patient_updates. Confirm 4 records, 4 distinct patient IDs, and no errors or empty values.

Keep the profiling views available while cleaning. A profile identifies a value that needs investigation; the field definition and cleaning rule decide the correction.

2.3.7 Clean a six-row table

Copy the following six-row table into a new worksheet named messy_raw; do not correct it while copying.

Copy the received values in Table 4 exactly, including the spaces, duplicate row, invalid number, and blank.

Table 4: Consultation records with data-quality errors.

`consultation_id`	`patient_id`	`procedure`	`actual_duration_min`	`status`
`C101`	`P001`	`Intake`	35	`completed`
`C102`	`P002`	`FOLLOW-UP`	50	`Completed`
`C102`	`P002`	`FOLLOW-UP`	50	`Completed`
`C103`	`P999`	`Intake`	-4	`Complete`
`C104`	`P003`	`intake`	`forty`	`Completed`
`C105`	`P004`	`Follow-up`		`Cancelled`

The cleaned table should contain one copy of C102, standardized procedure and status text, and flags for invalid durations, missing completed durations, and unmatched P999. Follow these steps:

Select the table, press Ctrl+T, confirm headers, and name it MessyRaw. Add a worksheet named correction_log with columns source_row, field, received_value, action, and reason.
Select a cell in MessyRaw, then select Data > From Table/Range. Name the query q_messy_raw. Keep actual_duration_min as Text while inspecting the received values. If an automatic Changed Type step has already produced an error for forty, remove that step and set this column to Text. Perform the deliberate numeric conversion in step 7.
Use Add Column > Index Column > From 1 so that every exception can be traced to its received row.
Select procedure; use Transform > Format > Trim, then Clean, then Capitalize Each Word. The surviving values must be Intake or Follow-Up.
Select status and use Transform > Format > Trim. Choose Transform > Replace Values. Find completed and replace it with Completed. Under Advanced options, select Match entire cell contents, then choose OK. Repeat for Complete, keeping the same whole-cell setting. This setting prevents a replacement inside an already correct value. The allowed values are now Completed and Cancelled.
Select the consultation_id heading, hold Shift, and select status. Confirm that all five received fields are selected and the added Index column is excluded. Choose Home > Remove Rows > Remove Duplicates. Confirm that one exact duplicate of C102 is removed and five records remain. Select Home > Close & Load > Close & Load To… > Only Create Connection > OK to return to Excel. Record the duplicate removal in correction_log. In Data > Queries & Connections, right-click q_messy_raw and choose Edit to continue cleaning.
Set actual_duration_min to Whole Number using its heading’s type icon. Select the error cell’s empty area to read the conversion details for forty. Note its Index and error details. Select Home > Close & Load to save the connection-only query and return to Excel. In correction_log, record its Index, the field, received value forty, replacement with null, and reason Cannot convert received text to a number. Reopen q_messy_raw through Queries & Connections > Edit. Select actual_duration_min, right-click its heading, and choose Replace Errors. Enter null and choose OK. This replaces the conversion error; the cancelled record’s existing null remains unchanged.

Select Add Column > Custom Column, name it duration_issue, and enter Listing 7 in the formula box. Select OK, then set the new column to Text. The null check runs first so that a missing value is handled before the comparison with zero.

Listing 7: Flag a missing completed duration or a negative duration while retaining the record.

if [actual_duration_min] = null then
    if [status] = "Completed" then "Missing after conversion" else "OK"
else if [actual_duration_min] < 0 then "Invalid"
else "OK"

Select Home > Close & Load to save the query and return to Excel. Create a worksheet named valid_patients with headings patient_id and region in A1:B1. Enter the four valid pairs in the round-1 JSON file: P001/East, P002/North, P003/West, and P004/South. Convert them to a Table named ValidPatients, import it with Data > From Table/Range, and name the query q_valid_patients. Return to q_messy_raw. Use Home > Merge Queries to merge q_valid_patients into the cleaning query on patient_id with a left outer join and expand region only. A null region exposes unmatched P999; record the exception. Do not invent a patient or silently delete the row.
Name the cleaned query q_messy_clean and select Home > Close & Load. In Excel, right-click q_messy_clean in Queries & Connections and select Load To… > Table > New worksheet > OK. Select the output and use Table Design > Table Name to name it MessyClean. Keep messy_raw, MessyClean, and correction_log together so that every change remains auditable.

Compare MessyClean with Table 5. Power Query may retain either Index for the identical C102 copies; keep the surviving Index for provenance. The received fields and expected issue flags are the same.

Table 5: Expected five-row cleaning result. Empty duration and region cells contain null in Power Query. The Index column remains in the workbook for provenance.

`consultation_id`	`patient_id`	`procedure`	`actual_duration_min`	`status`	`duration_issue`	`region`
`C101`	`P001`	`Intake`	35	`Completed`	`OK`	`East`
`C102`	`P002`	`Follow-Up`	50	`Completed`	`OK`	`North`
`C103`	`P999`	`Intake`	-4	`Completed`	`Invalid`
`C104`	`P003`	`Intake`		`Completed`	`Missing after conversion`	`West`
`C105`	`P004`	`Follow-Up`		`Cancelled`	`OK`	`South`

Standardize a value when the rule is known. Flag a value when its correction is unknown, and remove a row only when it meets the stated duplicate rule.

2.3.8 Combine related data

After importing, combine tables only when their grains and keys are clear. Power Query Merge Queries joins related tables before loading. XLOOKUP can retrieve fields after loading. Both approaches require compatible keys and an explicit response to unmatched or duplicated identifiers.

For the patient workbook, the consultation query can be merged with the patient query on patient_id. Verify that the merge does not change the number of consultation rows unless one-to-many expansion is intended.

Check keys before the merge

Check the patient keys before merging. A duplicate patient key can multiply consultation rows when the matched records are expanded.

Count records for each patient key

Open q_patient_updates through Data > Queries & Connections > Edit. In the editor’s Queries pane, right-click it and choose Reference. Name the new query q_patient_key_check. Leave the original query’s four columns unchanged for the merge.
Select patient_id and choose Home > Group By. In the Basic dialog, choose patient_id under Group by, enter patient_rows as the New column name, and select Count Rows as the Operation. Choose OK.
Inspect the four groups. Each patient_rows value must be 1. Open that column’s filter and choose Number Filters > Greater Than. Enter 1 and choose OK. No rows should remain. A row here identifies a duplicate key that must be investigated before joining.
Select Home > Close & Load > Close & Load To… > Only Create Connection, then OK. This check stays available in Queries & Connections without adding a worksheet Table. Reopen it after each source round to confirm that it remains empty. With the final filter removed temporarily, round 2 has six groups, each with a count of 1; restore the greater-than-1 filter.

Inspect the merge command and selected fields

Use the sequence in Microsoft’s merge queries and join tables tutorial. Then use Figure 3 to identify the current Power Query workspace before selecting both keys.

Current Power Query workspace showing data preparation and merge checks — Figure 3: The current Power Query workspace used to inspect types, profiles, and merge results.

Use Merge Queries as New here and retain the loaded source Tables for inspection. Complete the duplicate-key check above before following the merge.

Reopen q_consultations in Power Query. Select Home > Merge Queries > Merge Queries as New. In the merge dialog, choose q_consultations in the upper preview and q_patient_updates in the lower preview. The upper query is the left side of the join.
Select the patient_id heading in each preview. Both keys must have type Text. Under Join Kind, choose Left Outer. This retains every consultation, including one without a matching patient. Inspect the match indication; all six round-1 consultation rows should match a patient. Choose OK.
The new query has a column containing nested Table values. Select its expand icon, choose only region and risk_level, and clear Use original column name as prefix. Choose OK. The added headings must be exactly region and risk_level.
Name the result q_analysis in Query Settings. Select Home > Close & Load > Close & Load To… > Table > New worksheet, then OK. Use Table Design > Table Name to name the output AnalysisData.
Confirm six output records. Inspect the region and risk_level filter lists for blanks. An unmatched key produces null in the query preview and a blank in the loaded worksheet. Clear any inspection filters afterward.

The query name and the worksheet Table name identify different objects. contact_preference remains available in PatientUpdates but is not copied into AnalysisData because no required measure uses it. The supplied patient keys are unique, so the join must preserve the consultation count. Treat an unexpected increase as a failed check even if Excel reports no query error.

If q_analysis already exists, inspect its Source and expanded-column steps instead of creating a second merged query. Investigate unmatched keys before using the combined report.

Choose which query results to load

Keep q_patient_key_check as Only Create Connection because it is an intermediate check. Load q_consultations as ConsultationsLive and q_patient_updates as PatientUpdates while learning so that you can inspect their records. Load the final q_analysis result as AnalysisData; the dashboard in PivotTables, charts, and dashboards uses that Table. Query names identify procedures, and Table names identify worksheet outputs.

2.3.9 Design refresh checks

A useful refresh check is specific enough to fail visibly. Record at least:

source filename and update time;
row count;
count of distinct identifiers;
earliest and latest date;
number of missing keys;
number of query errors; and
one control total, such as total completed duration.

Keep these checks on a worksheet named checks or control. A successful refresh means that the changes are explainable, not that every number remains the same.

2.3.10 Record a baseline and refresh

Enter these labels in control!A2:A6 and the formulas in B2:B6. These must remain formulas, not typed expected values.

Enter the control formulas in Table 6 and compare their results after each source round.

Table 6: Refresh controls and expected results.

Cell	Check	Formula	Round 1	Round 2
`B2`	consultation rows	`=ROWS(ConsultationsLive[consultation_id])`	6	9
`B3`	patient-update rows	`=ROWS(PatientUpdates[patient_id])`	4	6
`B4`	actual-duration total	`=SUM(AnalysisData[actual_duration_min])`	215	360
`B5`	unmatched patient IDs	`=COUNTBLANK(AnalysisData[region])`	0	0
`B6`	consultations marked `Review`	`=COUNTIF(AnalysisData[risk_level],"Review")`	1	5

On control, record the following checks. Use ROWS for row counts, COUNTIF for Completed status, SUMIFS and AVERAGEIFS for completed duration, and COUNTBLANK on the joined region to detect unmatched patients.

Use Table 7 to check the complete joined population and the completed-consultation measures after refresh.

Table 7: Expected patient-data checks for both source rounds.

Check	`Round 1`	`Round 2`
Consultation rows	6	9
Patient rows	4	6
Joined rows	6	9
Unmatched patients in joined data	0	0
Completed consultations	5	8
Completed actual minutes	215	360
Average completed minutes	43	45
Consultations classified `Review`	1	5

Keep control!B2:B6 intact. Enter the labels and formulas in Table 8 in unused rows below them. Use Date format for B13:B14 and display average minutes with one decimal place.

Table 8: Cell locations, formulas, and expected results for the completed-consultation controls.

Cell	Label in column `A`	Formula	`Round 1`	`Round 2`
`B9`	Joined rows	`=ROWS(AnalysisData[consultation_id])`	6	9
`B10`	Completed consultations	`=COUNTIF(AnalysisData[status],"Completed")`	5	8
`B11`	Completed actual minutes	`=SUMIFS(AnalysisData[actual_duration_min],AnalysisData[status],"Completed")`	215	360
`B12`	Average completed minutes	`=AVERAGEIFS(AnalysisData[actual_duration_min],AnalysisData[status],"Completed")`	43	45
`B13`	Earliest consultation date	`=MIN(AnalysisData[consultation_date])`	2026-09-28	2026-09-28
`B14`	Latest consultation date	`=MAX(AnalysisData[consultation_date])`	2026-10-03	2026-11-03

Record the verified source round and filenames in control!A16:B18. Keep the source file’s update time separate from the latest consultation date.

Record the minimum and maximum consultation dates as well. The synthetic records in round 1 run from 28 September to 3 October 2026; round 2 extends to 3 November 2026.

Copy round 2 over the two files in data/current, retaining the stable filenames. Select Data > Refresh All and wait until the queries finish. Open Data > Queries & Connections. Wait until the loading indicators for the source and analysis queries finish. Inspect errors and the loaded row counts, reopen q_patient_key_check to confirm an empty result, and compare every control formula. A completed loading operation alone does not establish that the data passed its checks. The dependent PivotTables will be refreshed after query completion in the dashboard procedure. P002 changes from Standard to Review. Explain why that correction affects two earlier consultations as well as why three additional consultations appear.

If a source file moves, use Data Source Settings > Change Source to repair the path. Refresh and repeat the checks. Do not paste data over the query output or replace a failed refresh with a reported zero.

2.3.11 Exercises

2.3.Q1 Diagnose import and refresh faults

Return to Patients_live_refresh.xlsx before testing its patient-data connections below.

Save Patients_live_refresh.xlsx. Close and reopen it, select Refresh All, and confirm that the connection still works.
Create a subfolder named relocated_source and move consultations.csv into it. Select Refresh All and confirm the path error. Use Data > Get Data > Data Source Settings > Change Source to select the relocated file, refresh, and confirm the round-2 controls. Move the file back to its original folder, use Change Source again to restore the original path, refresh, and confirm the same controls.

If a source file was moved, do not rebuild the query or paste over the output. Open Data > Get Data > Data Source Settings, select the file source, and choose Change Source. Point to the intended stable file, refresh, and repeat every control check. In Power Query Editor, the equivalent route is File > Options and settings > Data source settings. Record the repaired path in the workbook’s read_me or control sheet.

Run each fault test separately in a disposable copy of the project folder. Open its workbook and use Data Source Settings > Change Source to point both queries to that copy’s data/current files. Confirm the round-2 baseline before changing anything. Keep the received files in data/raw unchanged.

In the working CSV, change C009’s patient_id from P006 to P999. Refresh and check for one unmatched region. Record the failed check. Replace the working file with the unchanged round-2 CSV, restore the stable filename, and refresh to confirm zero unmatched records.
In the working JSON, duplicate the complete P001 object, retaining valid JSON commas and brackets. Refresh and inspect q_patient_key_check. It must show P001 with patient_rows equal to 2. Expanding that join can produce 11 rows instead of 9 because P001 has two consultations. Restore the round-2 JSON and confirm an empty key check and nine joined rows.
Replace C009’s actual duration with forty in the working CSV. Refresh, reopen q_consultations, and inspect the conversion error in its numeric type step. A loaded error may appear as a blank or prevent an update; inspect the query rather than accepting a worksheet total alone. Restore the received numeric value by replacing the working CSV from data/raw, then refresh and confirm all round-2 controls.
Use Data > Get Data > From File > From Text/CSV to import the received data/raw/round-1/consultations_round_1.csv. Select Transform Data, name the query q_round1_append_test, and use Close & Load To… > Only Create Connection. Repeat for data/raw/round-2/consultations_round_2.csv, naming it q_round2_append_test. Reopen q_round1_append_test in the editor and choose Home > Append Queries > Append Queries as New. Select Two tables, choose those two queries, and select OK. Name the result q_appended_snapshots_test. It has 15 rows because the six earlier records are repeated. Compare that count with the nine-row replacement result. Close and load the test as a connection only. After recording the finding, delete q_appended_snapshots_test and then its two temporary source queries from Queries & Connections. Keep q_analysis connected to the stable files.

After each test, restore both working inputs from data/raw/round-2 and repeat the controls. Refresh once more with unchanged sources and confirm unchanged counts and totals. Close the disposable workbook, return to the original Patients_live_refresh.xlsx, and verify its original stable paths and round-2 results before saving.

A refreshable connection repeats a procedure on request; it is not a continuous real-time feed. PivotTables, charts, and dashboards adds the dependent PivotTable and chart.

2.3.M1 Select the correct import interpretation

A JSON source opens as a List containing one Record per patient. What should you normally do first?

Convert the List to a Table and expand the Record fields.
Paste the raw JSON text into one worksheet cell.
Rename the file with a .csv extension.
Delete every nested field before inspecting it.

Solution

A. The operation preserves one record per patient and exposes its fields. Further expansion depends on the intended table grain.

2.3.M2 Diagnose a failed refresh

A query refreshed yesterday but reports “file not found” today. Which check should come first?

Whether the chart color changed
Whether the source file was moved or renamed
Whether the workbook uses bold headers
Whether the PivotTable contains a slicer

Solution

B. File-based queries depend on a path. Verify the source location before changing transformations or downstream outputs.

2.3.M3 Interpret a changing control total

After a documented full replacement, the row count rises from six to nine and the latest date moves forward. What is the best conclusion?

The refresh must be wrong because the numbers changed.
The change is plausible but must be reconciled with the source update.
Delete the three new rows.
Convert the query output to fixed values.

Solution

B. Refresh checks are intended to expose and explain changes. A changed total is not automatically an error.