Power Query Problems

How to Organize Excel Property Codes by Month Using Power Query

Natalie Taylor Oct 10, 2026 868 views

Question details

The user needs to organize a list of property codes and dates so that the months appear as column headers, with the corresponding property codes listed beneath each month.

How to Organize Excel Property Codes by Month Using Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a custom report layout where dates are grouped into month columns and related text values (property codes) are dynamically listed underneath them.
Observed behavior: Standard pivot tables successfully group dates by month but aggregate data into numbers, failing to output the exact layout of text-based property codes directly under the headers.

Before you start

Ensure your source data is formatted as an official Excel Table (press Ctrl+T) and contains distinct column headers for 'Date' and 'Property Code' before importing it into Power Query.

Solution 1Recommended

Use Power Query to Group by Month and Pivot Columns

Extract the month name from your date column, group the property codes by month, and pivot the data to display the months as headers with the text values below.

Power Query is the most efficient tool for restructuring lists. By grouping all rows belonging to a specific month, you can extract the property codes and pivot the columns without aggregating the text into counts.

Load Data into Power Query

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

Extract the Month Name

Select your 'Date' column. Go to the 'Add Column' tab, click on 'Date', hover over 'Month', and select 'Name of Month'. This adds a new column with the month names.

Group Rows by Month

Select the newly created Month column. Go to the 'Transform' tab and click 'Group By'. Set the grouping to the Month column, name the new column 'GroupedCodes', and select 'All Rows' as the operation.

Extract Text Values

Go to the 'Add Column' tab and click 'Custom Column'. Enter the formula: Text.Combine([GroupedCodes][Property Code], ", ") to merge the property codes into a single comma-separated list. Click OK, then remove the 'GroupedCodes' column.

Pivot the Month Column

Select the Month column, go to the 'Transform' tab, and click 'Pivot Column'. In the dialog box, choose your Custom Column as the Values column. Expand 'Advanced options' and select 'Don't Aggregate', then click OK.

Load the Data back to Excel

Click 'Close & Load' on the 'Home' tab in the Power Query Editor to export the transformed month-header layout back into a new Excel worksheet.

Use Power Query to Group by Month and Pivot Columns

Formatting Tip: If you prefer the property codes to appear on separate rows rather than in a single cell separated by commas, you will need to add an Index column grouped by month before pivoting.

Solution 2

Use Dynamic Array Functions (Excel 365 & 2021)

For users who are not comfortable with Power Query, Excel's modern array functions can filter and group the property codes directly on the worksheet.

Free Microsoft Office alternative

Try WPS Office for Effortless Spreadsheet Management

If complex Power Query tasks and expensive Microsoft Office subscriptions are holding you back, consider switching to WPS Office. It provides a lightweight, highly compatible alternative with powerful built-in pivot tables and dynamic array formulas to organize your data efficiently at no cost.

1. Download WPS Office: Visit the official WPS website and click the 'Download WPS Office Free' button to get the installer package.
2. Install the Application: Run the downloaded file and follow the on-screen instructions to quickly install the software on your computer.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing .xlsx files to instantly begin organizing and analyzing your data.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv) without data loss.Familiar user interface that requires zero learning curve for Excel users.Lightweight software design ensures fast performance on older or slower devices.Powerful built-in formulas and Pivot Tables for robust data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Can a standard Excel Pivot Table list text values instead of numbers?

By default, standard Excel Pivot Tables aggregate data and display numerical values such as sums or counts. To display text values like property codes, you need to use Power Query to restructure the data or utilize the Data Model (Power Pivot) with DAX functions like CONCATENATEX.

How do I extract just the month from a date column in Power Query?

In the Power Query Editor, click on your date column, navigate to the 'Add Column' tab, click the 'Date' dropdown, select 'Month', and then click 'Name of Month'. This isolates the month text needed for column headers.

What if multiple property codes belong to the exact same month?

When grouping by month in Power Query, select 'All Rows' as your operation. You can then add a custom column using the Text.Combine function to merge all property codes for that month into a single cell, separated by a comma or a custom delimiter.

Excel & Data Problems