Power Query Problems

Choose Power Query or DAX for an Excel Data Model Calculation

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Determine whether a highlighted result should be calculated in Power Query or with DAX, then identify the information needed to write the formula.

Choose Power Query or DAX for an Excel Data Model Calculation
Product
Microsoft Excel Power Query and Power Pivot
Device & OS
not provided
Scenario
A user needs to calculate a highlighted result in a data model but has not supplied the source columns or expected output.
Observed behavior
The goal is to choose the appropriate calculation method and produce the expected result from the available data.
Before you start

Identify the highlighted calculation and collect sample rows, column names, and the expected results. Note whether the result must change when a PivotTable filter or slicer changes.

Solution 1Recommended

Use Power Query to prepare or calculate values during import

Choose this approach when the result is a row-level transformation needed before the data is loaded into the model.

1
Open the source data

In Excel, select a cell in the source table and choose Data > From Table/Range to open Power Query Editor.

2
Add the calculation

In Power Query Editor, choose Add Column > Custom Column. Enter a column name and an M formula based on the source columns and expected result.

3
Check and load the result

Review the preview, then use Home > Close & Load To to choose where to load the result. Select Add this data to the Data Model if the calculation is needed in the model.

Use Power Query to prepare or calculate values during import
Formula details are needed: A specific M formula requires the source column names, sample values, expected output, and the rule behind the highlighted calculation.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move everyday spreadsheet work. It does not resolve Excel-specific Power Query or DAX issues.

Open and edit Microsoft Excel XLSX and XLS filesUse spreadsheet formulas for everyday calculationsNavigate a familiar interface when moving from Microsoft OfficeWork with a lightweight, free office suite
QA img-9

Frequently Asked Questions

When should I use Power Query instead of DAX?

Use Power Query for transformations performed as data is imported or refreshed, such as adding a derived source column. Use DAX for calculations in the data model, particularly measures that respond to report filters.

Should the highlighted result be a DAX measure or a calculated column?

Use a measure when the result should change with PivotTable filters or slicers. Use a calculated column when you need a stored row-level value in the model.

What information is needed to write the exact formula?

Provide the source column names, sample rows, expected results, the highlighted calculation, and the relevant table relationships. Also specify whether the output should respond to filters.

Can I use WPS Office to fix an Excel Power Query or DAX formula?

WPS Office can open and edit Excel-format spreadsheets, but it does not fix Excel-specific Power Query or DAX issues. Use Excel's Power Query Editor or Power Pivot to create and troubleshoot those calculations.