Fix Power BI Conditional Column Showing Only Text Operators
Question details
The user is unable to use numeric operators like 'Greater than' or 'Less than' when creating a conditional column because only text-based operators are displayed.

- Product
- Power BI Desktop
- Device & OS
- not provided
- Scenario
- Creating a conditional column in the Power Query Editor to apply logical rules to a specific data field.
- Observed behavior
- The conditional column dialog displays text-based operators such as Equals, Begins with, and Contains, but completely omits numeric operators.
Ensure you have the Power BI Desktop file open and are actively working within the Power Query Editor window where your target table is loaded.
Change the Column Data Type to Numeric
Converting the column's data type to a numeric format ensures Power Query recognizes it as a number, which unlocks the mathematical operators in the Conditional Column dialog.
By default, Power Query may import data and assign a 'Text' data type to your column, especially if the source data contains mixed characters or unexpected formatting. When a column is formatted as text, the Conditional Column feature restricts you to text-based logical operations.
In Power BI Desktop, click on 'Transform Data' from the Home ribbon to open the Power Query Editor.
Find the specific column you want to use for your conditional logic. You will likely see an 'ABC' icon on the left side of the column header, indicating it is currently formatted as text.
Click the 'ABC' data type icon in the column header and select 'Whole Number', 'Decimal Number', or another appropriate numeric format from the dropdown menu.
Navigate to the 'Add Column' tab and click 'Conditional Column'. Select your newly formatted column, and you will now see numeric operators like 'is greater than' and 'is less than' in the operator dropdown.

Discover WPS Spreadsheet for Seamless Data Analysis
While Power Query handles complex data modeling, WPS Office offers a robust Spreadsheet application for everyday data manipulation, conditional formatting, and logic functions. It is highly compatible with Microsoft Excel formats and completely free for basic use.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing data file.
- 2. Use conditional formulas: Select an empty cell and type an IF formula, such as =IF(A2>100, "High", "Low"), to apply numeric conditions effortlessly.
- 3. Apply logic across rows: Drag the fill handle down to apply your conditional logic to the rest of your dataset.

Frequently Asked Questions
Why does Power Query automatically show my numbers as text?
Upon importing data, Power Query analyzes the first few rows to determine the data type. It often defaults to reading columns as Text if it detects mixed data types, empty values, or specific formatting elements from the source file.
What should I do if changing the data type to numeric results in errors?
If converting to a numeric type produces 'Error' values in your cells, the column likely contains non-numeric characters such as currency symbols, spaces, or text strings. You must use 'Replace Values' or 'Extract' to clean the data before changing the type.
Can I use multiple conditions in a single conditional column?
Yes. In the Conditional Column dialog, you can click the 'Add Clause' button to include additional IF and ELSE IF statements, allowing you to evaluate multiple logical layers in one go.
Does this issue happen in Excel Power Query as well?
Yes, because Excel and Power BI use the same underlying Power Query engine. The solution is identical: ensure the column data type is set to numeric in the Excel Power Query Editor before adding a conditional column.




