Power Query Problems

How to Normalize Daily Excel Data Using Power Query

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs to transform horizontally structured daily Excel data, which contains merged officer names and dates, into a normalized, flat tabular format containing Serial Code, Type, Date, and Officer columns.

Product: Excel
Device & OS: not provided
Scenario: Processing daily reports received via email as Excel workbooks to make the data ready for database-style analysis.
Observed behavior: The current data is arranged horizontally with merged fields, making it difficult to sort, filter, or analyze using standard database logic.

Before you start

Ensure you have the source Excel workbook saved in a stable, easily accessible folder on your computer, as Power Query requires a fixed file path to automate daily refreshes.

Solution 1Recommended

Unpivot and Normalize Data using Power Query

Use Power Query's Unpivot feature to quickly transform your horizontal layout into a normalized, tabular format.

Power Query can import data from various sources, but establishing a consistent source file format and location is the most important starting point for daily automation. By unpivoting the columns, you convert the wide horizontal data into a vertical list of records.

Import the Data

In Excel, go to the 'Data' tab on the ribbon, click 'Get Data', select 'From File', and then click 'From Workbook'. Locate and import your daily source file.

Unpivot the Columns

Once the Power Query Editor opens, hold down the Ctrl key and select the columns you want to keep static (e.g., Serial Code and Type). Right-click one of their headers and select 'Unpivot Other Columns'.

Rename and Format

Rename the newly created 'Attribute' and 'Value' columns to 'Date' and 'Officer', respectively. You can double-click the column headers to rename them.

Load and Automate

Click 'Close & Load' in the top-left corner to return the normalized data to your spreadsheet. For future daily updates, simply overwrite the source workbook file in your folder with the new day's file, then go to the Excel 'Data' tab and select 'Refresh All'.

Daily Automation Complete: As long as the structure of the incoming email attachment remains consistent, the 'Refresh All' button will automatically apply these unpivoting steps to the new data every day.

Free Microsoft Office alternative

Analyze Daily Data Efficiently with WPS Spreadsheet

While Microsoft Excel offers Power Query, WPS Office provides a lightweight, highly compatible alternative that covers your essential daily data processing needs without the heavy resource usage. Easily open, organize, and analyze your reports using familiar spreadsheet tools.

1. Download and Install: Get WPS Office for free from the official website and install it on your computer.
2. Open Your Daily Data: Launch WPS Spreadsheet and open the daily Excel workbook you received via email.
3. Organize and Summarize: Select your data range, go to the Insert tab, and choose Pivot Table to quickly summarize and manage your daily entries.

Fully compatible with Microsoft Excel (.xlsx, .xls) filesLightweight application that runs smoothly on older devicesFamiliar user interface requires no learning curvePowerful built-in pivot tables and data sorting features

microsoft office alternative - wps office

Frequently Asked Questions

Why do I need to unpivot my daily Excel data?

Unpivoting converts data from a wide, horizontal layout into a tall, vertical table. This normalized format is essential for creating pivot tables, running database queries, and performing accurate, error-free data analysis.

How do I refresh the Power Query data when I get a new file tomorrow?

Save the new daily file in the exact same folder with the exact same file name as the original source workbook, overwriting it. Then, open your master workbook and click 'Refresh All' under the Data tab to automatically process the new data.

What should I do if the file path for my data source changes?

Open the Power Query Editor, go to the 'Applied Steps' pane on the right side, and click the gear icon next to the 'Source' step. You can then browse and select the new file location to update the File.Contents path for your query.

Excel & Data Problems