Power Query Problems

Convert Complex Excel Formula Logic to Power Query or DAX

Elise WilliamsElise Williams Nov 12, 2026 869 views

Question details

The user needs to reproduce nested Excel formula logic in Power Query or DAX and return business labels based on current and previous values.

Product
Microsoft Excel, Power Query, and DAX
Device & OS
not provided
Scenario
The formula compares current and previous records to classify orders or performance, with results such as New Order, Good Performance, or Reevaluate the Performance.
Observed behavior
The existing formula contains a possible missing argument and inconsistent conditions, so a direct conversion may produce incorrect classifications.
Before you start

Standardize the date and comparison-value formats, then refresh the source PivotTable or data model. Review the original formula for missing arguments and write each business rule as a separate condition before converting it.

Solution 1Recommended

Clarify and test the business rules before converting the formula

Use this approach when the nested formula has missing arguments, inconsistent conditions, or unclear comparisons between current and previous values.

A direct one-to-one conversion is unsafe until each condition has a defined order and outcome. Create sample records covering new orders, performance changes, follow-up cases, and reevaluation scenarios, then compare the expected label with the formula result.

1
Inspect the original formula

Select the formula cell in Excel and review the complete expression in the Formula Bar. Open the Formulas tab and choose Evaluate Formula to identify missing arguments or conditions that are evaluated unexpectedly.

2
Normalize dates and comparison values

Select the source range, open the Data tab, and use Sort and Filter or Text to Columns as needed to standardize dates and convert text numbers into numeric values. Refresh the PivotTable from the PivotTable Analyze tab after correcting the source.

3
Write a rule table

In a blank worksheet, create columns for the current value, previous value, comparison result, expected label, and rule priority. Add representative records for every output, including New Order, Good Performance, and Reevaluate the Performance.

4
Validate the expected outcomes

Compare the rule-table labels with the original formula results. Resolve overlapping conditions and confirm which rule should take priority before creating a Power Query custom column or DAX expression.

Why validation matters: Power Query and DAX can reproduce the logic only after the intended conditions are complete and unambiguous. An invalid or incomplete Excel formula should not be converted mechanically.
Free Microsoft Office alternative

Review and migrate complex spreadsheet logic with WPS Office

WPS Office provides a free, lightweight spreadsheet environment for reviewing nested formulas, standardizing dates, testing classification rules, and opening or saving common Microsoft Excel files. While Power Query and DAX are Microsoft data-model features, WPS helps prepare the workbook and supports a familiar migration path with strong format compatibility.

  1. 1. Open the workbook: Launch WPS Spreadsheet, choose Open, and select the XLS or XLSX workbook containing the nested formula.
  2. 2. Inspect the formula logic: Select the formula cell and review the expression in the formula bar. Create a nearby rule table with sample current values, previous values, and expected labels.
  3. 3. Standardize and test the source data: Use the Data tab to sort or filter the records, verify date formats, and compare the calculated labels with the expected outcomes before migrating to Power Query or DAX.
Review nested spreadsheet formulas and organize business-rule test cases in a familiar interface.Use a lightweight spreadsheet workflow to standardize dates and compare current and previous values.Open and save Microsoft Excel XLS and XLSX files for easier migration and collaboration.Preserve familiar spreadsheet formatting and formulas when moving between WPS Office and Microsoft Excel.
microsoft office alternative - wps office

Frequently Asked Questions

Can a nested Excel formula be converted directly to Power Query?

Not reliably if the formula has missing arguments or overlapping conditions. First clarify the rule order and validate sample records, then recreate the logic in Power Query Editor through Add Column > Custom Column using if...then...else expressions.

Should I use a Power Query custom column or a DAX expression?

Use a Power Query custom column when the label should be created during data import or refresh. Use a DAX calculated column for row-level labels in a data model, or a DAX measure when the result must respond to report filters.

How do I compare current and previous values in Power Query?

Load the data with Data > From Table/Range, confirm the sort order and data types, and create a Custom Column after the previous-value field is available. The expression can then compare fields such as [CurrentValue] and [PreviousValue].

Why might the converted labels be incorrect?

Incorrect labels commonly result from inconsistent date or number types, a missing formula argument, overlapping conditions, or an incorrect rule priority. Test each condition against representative records before refreshing the final query or PivotTable.

Can WPS Office run Power Query or DAX?

WPS Office can help review formulas, prepare spreadsheet data, and open or save XLS and XLSX files, but Power Query and DAX data-model features are Microsoft Excel capabilities. Use WPS for preparation and migration, then implement those features in a compatible Microsoft data-model environment.