Illustration of a desk with a calculator, a laptop and data charts on transparent screens

Power Query and Power Pivot for actuarial and finance teams

  • Darren Vinales
  • October 19, 2026

Actuarial and finance teams in insurance spend a large part of each reporting cycle preparing data: exporting claims and premium files, cleaning them, matching them to the accounts and reshaping them for analysis. Power Query and Power Pivot are built into Excel and can take much of that routine work off your hands, without new software or a large project.

What the two tools do

  • Power Query connects to data sources such as databases, CSV extracts and other workbooks, and records the steps you take to clean and reshape the data. When new data arrives, you refresh the query and the same steps run again.
  • Power Pivot holds the prepared data in a data model inside Excel. Tables are linked by relationships, and calculations are written once as measures using DAX (Data Analysis Expressions), then used in PivotTables and charts.

Where they help in insurance work

  • Claims triangles. Power Query can combine payment and case estimate data across periods and shape it into the layout needed for triangles, ready to refresh each quarter.
  • Reconciliations. Claims and premium data can be matched to the general ledger in a repeatable way, with differences shown in a separate table for review.
  • Premium and exposure analysis. Policy data from different systems or years can be appended into one table and summarised by class, year or channel.
  • Reporting packs. PivotTables built on a Power Pivot model can feed management and board reports without copying figures by hand.

Practical tips

  1. Keep raw data untouched. Load extracts as they arrive and do all cleaning in Power Query, so you can always trace a figure back to its source.
  2. Name every step. Rename query steps to say what they do, such as “Remove cancelled policies”. It makes the process easier to review and hand over.
  3. Replace lookups with relationships. Instead of long VLOOKUP or XLOOKUP formulas, load lookup tables into the data model and relate them to your main tables.
  4. Define measures once. Write key figures such as paid claims, incurred claims or earned premium as measures, so every report uses the same definition.
  5. Add checks to the process. Build control totals and row counts into your queries so that a missing or duplicated file is spotted on refresh.
  6. Document the refresh. Write down which files are needed, where they come from and in what order to refresh, so the process does not depend on one person.

Knowing the limits

Power Query and Power Pivot work well for many reporting and analysis tasks, but workbooks still need to be stored, shared and controlled. When several people need the same reports, or reports need to refresh on a schedule, moving the model to Power BI is often the next step. Our article on moving insurance reporting from Excel to Power BI explains how to approach it.

How we can help

We use Power Query, Power Pivot and Python in our actuarial, finance and risk work, including reserve reviews and QRT preparation and filing , and we provide training through our business intelligence reporting and analytics service. To find out more, please contact us .

Related Posts

Illustration of a laptop showing bar charts and line graphs on a dashboard

Moving insurance reporting from Excel to Power BI

  • Darren Vinales
  • October 19, 2026

A practical guide for insurers moving claims, premium and reserving reports from Excel to Power BI: when to move, how to plan it and what to check.

Read More