Power Query Problems

Use Excel Power Query to Transfer Multiple Census Rows into a Template

Steve KSteve K Nov 18, 2026 868 views

Question details

Create a repeatable process to map census data into an underwriting template with one row per employee.

Transfer Multiple Census Rows into an Excel Template with Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Weekly census files arrive in different layouts, and source columns and multiple records must be converted to the template's structure.
Observed behavior
The desired result is a standardized template row for each employee that can be refreshed with minimal manual work.
Before you start

Identify the template's required columns, a reliable employee identifier, and the rules for combining multiple records. Check which source layouts recur and whether fields such as employee IDs need to remain as text.

Solution 1Recommended

Transform and Refresh Census Data with Excel Power Query

Use Power Query to standardize source fields and produce one output row per employee.

The grouping and pivoting rules depend on what each source row represents. Decide how to handle duplicate values and missing employee IDs before combining records.

A saved query can refresh compatible files, but substantially different source layouts may need separate queries or changes to the transformation steps.

1
Import the census file

In Excel, select Data > Get Data and choose the appropriate source, such as From File > From Workbook or From Text/CSV. Select the data and choose Transform Data to open Power Query Editor.

2
Map and clean the columns

In Power Query Editor, rename source columns to match the template, remove unnecessary columns, and use column filters to exclude records that should not be included. Set appropriate data types, keeping identifiers as text when leading zeros matter.

3
Combine employee records

Select the employee identifier and use Home > Group By when records must be grouped. If values from separate rows need their own template columns, select the category column and use Transform > Pivot Column; choose an aggregation rule appropriate for duplicate values.

4
Arrange the template fields

Rename and reorder the resulting columns in Power Query Editor to match the underwriting template. Add missing fields as needed, then check that each employee has one row and that the values are in the correct columns.

5
Load and refresh the results

Select Home > Close & Load To to load the results to a worksheet or an appropriate blank output area. For the next compatible file, update the query's source if its path changes, then select Data > Refresh All and review the output before using it in the template.

Transform and Refresh Census Data with Excel Power Query
Check the template layout: Loading a query creates or updates an output table; it does not automatically populate every cell in a preformatted template. Verify the destination and copy or link the output into the template if required.
Free Microsoft Office alternative

Review Census Templates in WPS Spreadsheets

WPS Office is a free, lightweight alternative for opening and reviewing spreadsheet templates with a familiar interface. Its Microsoft Office format compatibility makes it easy to move existing files; Power Query-based transformations may still require Excel.

Open and review employee census tables and underwriting templates.Work with Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface to make the transition from Office easier.Download a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

How do I refresh the query with a new weekly census file?

If the new file has a compatible layout and replaces the file at the same path, select Data > Refresh All. If the path changes, update the query's source settings before refreshing.

Can one query handle census files with different layouts?

A query can handle consistent variations if its transformation steps account for them. For substantially different column names or structures, create a separate query for each layout and standardize the columns before combining results.

How do I turn multiple records into one row per employee?

In Power Query Editor, use Home > Group By on a reliable employee identifier, or use Transform > Pivot Column to turn row categories into columns. Choose rules that match the data, especially when an employee has duplicate category values.

Will Power Query fill my existing formatted template automatically?

Not necessarily. Use Home > Close & Load To to choose an output location, then verify how the resulting table fits the template. You may need to copy or link the results into the formatted cells.