Power Query Problems

How to Classify New and Existing Business in Excel Power Query

Bushra Parveen Sep 27, 2026 870 views

Question details

The user needs to classify recent sales data as either 'New Business' or 'Existing Business' by checking if the same customer utilized the same revenue stream within the previous 12 months.

How to Classify New and Existing Business Using Excel Power Query

Product: Excel
Device & OS: not provided
Scenario: Analyzing and categorizing annual sales revenue streams using Power Query automation.
Observed behavior: Sales transactions must be dynamically classified based on historical data matching specific customer, revenue stream, and rolling 12-month date conditions.

Before you start

Ensure both your current sales dataset (e.g., 2024 sales) and historical sales data (e.g., 2023 sales) are properly loaded into Power Query as tables or connections.

Solution 1Recommended

Add a Custom Column with M Code Formula

Use a custom conditional column with Table.SelectRows to verify if previous matching transactions exist within the last 12 months.

This method directly evaluates historical tables against your current data row-by-row, ensuring accurate classification without needing complex DAX measures.

Open Power Query Editor

Launch the Power Query Editor in Excel and select your current (e.g., 2024) sales query from the Queries pane.

Add a Custom Column

Navigate to the 'Add Column' tab on the top ribbon and click the 'Custom Column' button.

Enter the Classification Formula

In the Custom Column dialog, enter the following formula: = if Table.RowCount(Table.SelectRows(#"2023", (x) => x[Customer Number] = [Customer Number] and x[REV STREAM 3] = [REV STREAM 3] and x[Invoice Date] >= Date.AddYears([Invoice Date], -1))) > 0 then "Existing Business" else "New Business"

Apply and Load

Click 'OK' to save the column, verify the categorization results in the preview window, and then click 'Close & Load' on the Home tab to output the dataset.

Query Performance Tip: For large datasets, evaluating Table.SelectRows on every row can be slow. Consider buffering your historical 2023 query (using Table.Buffer) to significantly improve query evaluation speeds.

Solution 2

Use a DAX Calculated Column in Power Pivot

Append both current and historical tables into a single data model and evaluate them using a DAX calculated column.

Solution 3

Perform a Left Outer Join

Merge current and historical queries to find matching customer and revenue streams, then filter by the invoice date.

Free Microsoft Office alternative

Manage Your Sales Data Efficiently with WPS Office

While Power Query is a Microsoft-specific tool, WPS Office provides a lightweight, highly compatible, and free alternative for comprehensive data analysis. WPS Spreadsheet supports complex formulas, advanced Pivot Tables, and seamless integration for managing large sales datasets without the heavy resource requirements of traditional Office suites.

1. Download and Install: Visit the official WPS website to download and install the free WPS Office suite on your computer.
2. Open Your Data File: Launch WPS Spreadsheet and seamlessly open your existing Excel workbook to access your sales and revenue data.
3. Analyze Data with Advanced Tools: Utilize built-in advanced formulas, conditional formatting, and Pivot Tables to efficiently categorize and analyze your business revenue.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .csv) formats ensuring your sales data transfers seamlessly.Includes powerful built-in functions, Pivot Tables, and conditional formatting for advanced sales categorization and analysis.Free, lightweight, and requires minimal system resources, handling large datasets efficiently.Familiar spreadsheet user interface ensures a zero-learning-curve transition for data analysts.

microsoft office alternative - wps office

Frequently Asked Questions

How can I improve the performance of this Power Query custom column formula?

For large datasets, the row-by-row evaluation of Table.SelectRows can cause significant slowdowns. You can improve performance by wrapping your historical 2023 query reference inside a Table.Buffer function, which loads the historical data into memory once rather than querying it for every single row.

Can I use DAX in Power Pivot instead of Power Query for this classification?

Yes. If both your current and historical tables are appended into the Excel Data Model, you can create a DAX calculated column using CALCULATE and COUNTROWS. DAX is highly optimized for this type of relationship logic and often evaluates much faster on millions of rows compared to Power Query.

What happens if there are null values in the revenue stream column?

Null values can cause the matching logic to fail or return inaccurate classifications, as comparing a null value to another null value in Power Query does not always behave like a standard string match. It is highly recommended to clean your dataset by replacing or filtering out nulls in both tables before applying the classification formula.

Excel & Data Problems