Power Query Problems

How to Automatically Transform Agent Hours into a Daily Excel Layout

John WilsonJohn Wilson Nov 10, 2026 869 views

Question details

Transforming source records containing agents, dates, and hours into a structured report with one row per agent and one column per day.

How to Automatically Transform Agent Hours into a Daily Excel Layout
Product
Excel
Device & OS
not provided
Scenario
Converting vertical raw log data into a horizontal daily attendance or hours summary report.
Observed behavior
Raw data records are listed vertically and need to be pivoted horizontally into daily columns while supporting automatic data refreshes.
Before you start

Ensure your raw data is formatted as an official Excel Table (press Ctrl+T) and has clear, distinct headers for Agent, Date, and Hours before applying any transformations.

Solution 1Recommended

Use Power Query to Automate the Daily Layout

Power Query is the most robust method for automating data transformations and pivoting rows into columns, especially for larger datasets.

By using the Pivot Column feature in Power Query, you can dynamically transform vertical dates into horizontal columns. This method ensures that your layout scales automatically as new dates are added to your source data.

1
Load data to Power Query

Select any cell inside your source data table, go to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

2
Select the Date column

In the Power Query Editor, click the header of the 'Date' column to highlight it.

3
Pivot the column

Navigate to the Transform tab and click 'Pivot Column'. In the dialog box that appears, choose 'Hours' as the Values Column.

4
Configure advanced options

Click to expand the Advanced options in the Pivot Column dialog, ensure the Aggregate Value Function is set to 'Sum', and click OK.

5
Load the result to Excel

Go to the Home tab and click 'Close & Load' to output the transformed daily layout to a new worksheet.

Use Power Query to Automate the Daily Layout
Auto-Refresh Capability: When new agent hours are added to the original source table, simply right-click your new output table and select 'Refresh' to instantly update the daily layout with the latest data.
Efficient Data Analysis

Easily Summarize Agent Hours with WPS Spreadsheet

WPS Spreadsheet provides highly intuitive PivotTable and data processing tools, allowing you to transform raw log data into professional daily reports effortlessly.

  1. 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your raw agent log data.
  2. 2. Insert a PivotTable: Select your data range, click the Insert tab on the top menu, and choose 'PivotTable'.
  3. 3. Arrange the fields: In the side panel, drag the 'Agent' field to Rows, 'Date' to Columns, and 'Hours' to Values.
  4. 4. Refresh when needed: Whenever new data is added, simply click the Data tab and select 'Refresh All' to update your daily layout.
Seamless compatibility with all Microsoft Excel formats (.xlsx and .xls)Powerful built-in PivotTable tools for fast data restructuringLightweight application that handles large datasets smoothlyFree to download with a familiar user interface for seamless migration
microsoft office alternative - wps office

Frequently Asked Questions

How can I make my transformed daily layout update automatically?

If you are using Power Query, right-click the green output table and select 'Refresh'. For PivotTables, right-click anywhere inside the PivotTable grid and click 'Refresh' after adding new data to the source table.

Can I use dynamic formulas instead of Power Query to achieve this layout?

Yes. In modern versions of Excel, you can use the UNIQUE formula to generate a list of agents for the rows and dates for the columns. You can then use the SUMIFS function inside the grid to calculate the total hours for each intersecting agent and date.

Why are my PivotTable dates grouping by month instead of showing individual days?

Excel PivotTables often group dates automatically to simplify reports. To show daily columns, right-click any date header in your PivotTable, select 'Group', ensure only 'Days' is highlighted in the list, and click OK.