Power Query Problems

How to Count Categories by Week for Dynamic Excel Charts

Elise Williams Sep 30, 2026 868 views

Question details

The user needs to count data categories (e.g., cat, dog, pigeon) per week from a dataset that grows by one column each week, and use this to feed an auto-expanding chart.

How to Count Categories by Week for Dynamic Excel Charts

Product: Microsoft Excel
Device & OS: not provided
Scenario: Summarizing weekly categorical data into a dynamic chart that automatically updates as new weekly columns are added to the source.
Observed behavior: The current wide data layout (adding new columns for weeks) does not naturally support standard PivotCharts, requiring a data transformation to automatically expand the chart.

Before you start

Ensure your raw data is formatted as an official Excel Table (by pressing Ctrl+T) so that Power Query can dynamically detect new columns as they are added.

Solution 1Recommended

Use Power Query to Unpivot and Summarize Data

Transform the growing weekly columns into a flat dataset to easily count categories and feed an auto-expanding PivotChart.

To make a chart dynamically expand when new weekly columns are added, you must normalize the data first. Power Query can "unpivot" these columns, turning your wide data into a tall, structured format suitable for charting.

Load data into Power Query

Click anywhere inside your Excel Table, navigate to the 'Data' tab on the ribbon, and select 'From Table/Range' to open the Power Query Editor.

Add an Index Column

Go to the 'Add Column' tab in the Power Query Editor, click 'Index Column', and choose 'From 1'. This creates a unique identifier for each row to prevent data collapse during the unpivot process.

Unpivot the weekly columns

Right-click the header of your new 'Index' column and select 'Unpivot Other Columns'. This transforms all your weekly columns into two new columns: 'Attribute' (the week) and 'Value' (the category).

Group and count the categories

Delete the 'Index' column. Then, go to the 'Transform' tab and click 'Group By'. Group by 'Attribute' and 'Value', name the new column 'Count', and set the operation to 'Count Rows'.

Load to a PivotChart

Click 'Home' > 'Close & Load To...', and select 'PivotChart'. As you add new weekly columns to your original table in the future, simply click 'Data' > 'Refresh All' to update the chart.

Use Power Query to Unpivot and Summarize Data

Dynamic Updates Enabled: Because you unpivoted 'Other Columns', Power Query will automatically detect and include any newly added weekly columns every time you refresh.

Free Microsoft Office alternative

Try WPS Office for Seamless Data Analysis and Charting

While advanced Power Query M code is highly specific to Microsoft Excel, WPS Office provides a lightweight, entirely free alternative with robust PivotTable features, wide format compatibility, and easy-to-use dynamic charting capabilities.

1. Download and Install WPS Office: Visit the official WPS website to download the free suite and install it on your device.
2. Open Your Excel Workbook: Launch WPS Spreadsheet and open your existing .xlsx files with zero formatting loss.
3. Create Dynamic Charts: Use the built-in Insert Chart and PivotTable features to efficiently summarize and visualize your categorical data.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv)Create dynamic charts and manage PivotTables effortlesslyFree to use with a familiar, ribbon-based interfaceLightweight application that runs smoothly on Windows, Mac, and Linux

Frequently Asked Questions

Will Power Query work if my data only 'looks' like a table but isn't formatted as one?

No. Power Query requires the source range to be explicitly converted into an official Excel Table (using Ctrl+T). If it is just a formatted range of cells, Power Query cannot dynamically track new columns.

Can I process only one specific section of a larger worksheet?

Yes. By selecting only the specific data range you want to analyze and converting just that section into an Excel Table, Power Query will isolate and process only the records within that Table.

Do I need to rewrite the formula when a new week is added?

Not if you use the Unpivot Other Columns method in Power Query. It automatically accounts for any new columns added to the Table. You only need to click 'Refresh All' on the Data tab to update your chart.

Excel & Data Problems