Create a typed Power Query pipeline — Smart Study Module 5

Replace a monthly copy/paste ritual with a refreshable transformation.

Outcomes

• Append same-schema payment files
• Merge against unique loan keys
• Quarantine errors rather than discard silently

Understand

Append stacks compatible rows; merge attaches columns by matching keys. Combining January and February files should produce nine rows, while merging a unique Loans table should preserve those nine rows.

Power Query records ordered transformation steps. Filter filenames/extensions before combining a folder; exclude temporary and output files. A sample-file transform applies to every combined file, so test schema drift.

Set types explicitly. A type conversion error belongs in a visible exceptions query. Silent Skip files with errors can omit a whole month while the report still looks polished.

Follow along

1. Download January and February CSVs.
2. In desktop Excel use Data → Get Data → From File → From Folder, or import separately and Append.
3. Set payment_date to Date and amount to Whole Number.
4. Merge Loans by loan_id, expand branch_id and verify nine rows.

Worked example

Table.TransformColumnTypes(Source, {{"payment_date", type date}, {"amount", Int64.Type}})

Append: 9 payment events; amount total ₹98,000; no unmatched loan keys.

Practice

How many rows should the appended clean payments contain?

Create a free personalized study plan · Download project instructions

Interactive practice runs in your browser.