Power Query: A No-Code Tool for Automating Data Cleaning by Liz DiLuzio

Date: Tuesday, March 17, 2026

Hello AEA365 readers! I’m Liz DiLuzio, Instructor at Evaluation + Learning Consulting.

Many evaluation teams spend a surprising amount of time doing the same data cleaning tasks over and over again:

Export the survey results.
Fix the column names.
Standardize values.
Match participants across datasets.
Recreate the same tables for reporting.

Individually, none of these steps are particularly difficult. But when they must be repeated every month across multiple programs or sites, they can consume hours of staff time.

This is where automation tools can help.

One tool I often introduce in trainings is Power Query, which is built directly into Excel and designed as a no-code way to automate repetitive data preparation tasks.

A real-world example

Last June, Dana Benjamin-Allen at Back of the Napkin Consulting reached out after remembering a workshop I had taught on Power Query. She wondered whether the tool might help streamline part of her workflow.

Dana and her team were managing survey data for multiple programs and sites. Each month, they needed to clean the survey files, match pre- and post-tests, and generate tables showing student progress on several social-emotional learning outcomes.

The work was routine but time-consuming. Each month required repeating the same series of steps so results could be shared with program leadership.

Using Power Query, we built a workflow that automated much of that process.

Through a series of queries, the system now:

• Fuzzy-matches pre- and post-test responses when names or IDs are slightly inconsistent
• Flags potential over-matching or under-matching so the analyst can quickly review and correct cases
• Generates tables showing who completed the pre-test, who completed the post-test, and who completed both
• Tracks when each survey was taken
• Produces preliminary tables showing score changes across SEL outcomes

Once the workflow was created, the team only needed to drop the new monthly data file into the workbook and refresh the queries. The same cleaning and matching steps run automatically.

Why Power Query is so useful for evaluators

Power Query is particularly valuable for evaluators who work primarily in Excel but want to automate repetitive data tasks without learning a programming language.

Some of the most common ways evaluators use Power Query include:

1. Combining multiple datasets. Evaluation data often arrives as separate spreadsheets from different sites or time periods. Power Query can automatically combine these files into a single dataset using joins and fuzzy matching, as needed.
2. Reshaping messy data. Many exported datasets require restructuring before analysis. Power Query makes it easier to pivot, unpivot, split, or reorganize data into a usable format.
3. Creating repeatable workflows. Perhaps the biggest advantage is reproducibility. Once a sequence of steps is built, the same process can be run again and again with new data.

Power Query sits in a useful middle ground. It allows evaluators to automate complex data preparation tasks while continuing to work in a familiar Excel environment. For teams that have not yet adopted R or Python, it can dramatically reduce the time spent preparing data for analysis.

Rad Resources

• In the month of May, we’ll be hosting 1.5 hour skill sprints on automating data cleaning with Power Query. Let us know which four topics you think we should cover.
• In the meantime, here are some solid resources to get you started:
  • Microsoft’s Power Query documentation and tutorials provide a helpful starting point for understanding the tool’s core features
  • This video gives a great overview
  • 7 tips for making PQ easy to use

Do you have questions, concerns, kudos, or content to extend this AEA365 contribution? Please add them in the comments section for this post so that we may enrich our community of practice. Would you like to submit an AEA365 tip? Please send a note of interest to aea365@eval.org. AEA365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators. The views and opinions expressed on the AEA365 blog are solely those of the original authors and other contributors. These views and opinions do not necessarily represent those of the American Evaluation Association, and/or any/all contributors to this site.