In Excel, a Power Query value transformation changes or cleans data inside a query before it is loaded to a worksheet or data model. Select a column in Power Query Editor, choose an operation on the Transform tab—such as changing its data type, replacing values, or trimming text—and then use Home > Close & Load; Power Query records the operation as a repeatable step without changing the original source data.
What value transformation means in Power Query
Power Query is Excel’s data-import and preparation tool. A transformation is an instruction applied to the data returned by a query. Value transformations affect the contents of a column or selected cells: for example, converting text that looks like a date into a real date value, replacing a misspelling, or removing spaces around text.
Power Query also has structural transformations. These change how data is arranged rather than simply changing a value. Examples include removing columns, filtering rows, splitting one column into two, grouping records, and pivoting or unpivoting data. In everyday use, people often call both kinds of operation “transformations.”
The crucial distinction is that Power Query does not normally edit the source workbook, CSV file, or database. It stores a sequence of steps in the query. When you refresh, Excel reads the source again and applies those steps to the latest available data. This makes the process useful for recurring reports: clean the data once, then refresh the query as new records arrive.
How a transformation is applied
In Excel for Microsoft 365 on Windows, start by creating or opening a query. For a range or Excel table, select a cell in it and choose Data > From Table/Range. To import an external file, use Data > Get Data and choose the relevant source. Excel opens Power Query Editor, where the preview shows the data that the query currently returns.
Select a column heading or cell, then choose an action from the ribbon. The exact choices depend on the selected data type and operation. As you make changes, the Applied Steps list records them. Selecting an earlier step lets you inspect the query at that point; you can also rename, edit, or delete steps. The final result is loaded with Home > Close & Load, or Home > Close & Load To when you want to choose a destination.
Each step is represented in the Power Query M language. You do not need to write M for common tasks: the interface creates the corresponding instructions. However, the formula bar or Advanced Editor can help you understand what a step does or adjust a query when the interface does not provide the exact behavior you need. The formula bar can be shown from the View tab in Power Query Editor.
Common value transformations
The right operation depends on what the values mean and what the next analysis needs. Typical choices include:
- Change data type: Convert a column to text, whole number, decimal number, date, date/time, or another supported type. Correct types are essential for sorting, calculations, filtering, and summaries.
- Replace values: Map inconsistent entries to a standard value, such as changing “In progress” and “In-Progress” to one agreed label. In Power Query Editor, select the column and use Transform > Replace Values.
- Trim and clean text: Trim removes leading and trailing spaces; Clean removes nonprinting characters. These can fix text that looks identical but fails to match because of hidden characters.
- Format text: Convert text to uppercase, lowercase, or proper case, or extract, split, or combine text where the task calls for it.
- Round or otherwise adjust numbers: Use numeric commands to round values or apply a suitable arithmetic transformation. Check whether rounding is appropriate for the analysis rather than rounding merely to make a display look tidy.
- Replace errors or handle missing values: Replace errors with a deliberate value, or filter and investigate them. A blank, a null, zero, and an error are different states and should not be treated as interchangeable.
Some commands operate on every row in a selected column, while others can target a specific value or use conditions. Before applying a change, consider whether the same rule is valid for the whole column. Replacing every occurrence of “-” with zero, for example, could alter a legitimate text code as well as a placeholder.
A practical example: standardizing imported sales data
Imagine a monthly CSV file with columns named OrderDate, Region, and Amount. Some dates arrive in day/month/year format, region names have extra spaces, and Amount is imported as text because a few entries contain currency symbols. A reliable preparation sequence could be:
- Open the CSV query in Power Query Editor and check the preview, column names, and automatically added steps. Automatic type detection is helpful, but it can guess incorrectly, especially when the preview contains only a sample of the rows.
- Select Region and choose Transform > Format > Trim. If invisible control characters are also an issue, use Transform > Format > Clean as a separate step. Review the result to ensure the labels still have the intended spelling.
- Set OrderDate to the Date type. If the dates are being interpreted with the wrong month and day, use the column’s context menu, choose Change Type > Using Locale, then choose Date and the locale whose date convention matches the source. A value such as 04/05/2026 can mean April 5 or May 4 depending on that convention.
- Clean Amount before converting it. If its values contain a consistent currency symbol or separator, remove or interpret those characters appropriately, then set the column to Decimal Number or the appropriate numeric type. Check several rows, including values with thousands separators and decimal places.
- Inspect errors and unusual values, then choose Home > Close & Load. Later, use Data > Refresh All to rerun the query against the source.
The example illustrates why changing a type is not always the same as changing how a value looks. A date displayed as 4/5/2026 may be a true date value with a number format, or it may be text containing those characters. A true date can be sorted chronologically and used in date calculations; text may sort alphabetically or fail in date operations. Power Query’s type setting addresses the underlying value, while worksheet number formatting controls how a loaded value is displayed.
Data types, locale, nulls, and errors
Data types tell Power Query how to interpret values. Text that contains digits is not automatically a number in every context: product codes such as 00127 should often remain text because converting them to a number removes the leading zeros. Conversely, a quantity stored as text may need conversion before it can be summed. Choose a type based on meaning, not just on the characters visible in the preview.
Locale matters when parsing dates and numbers. Regional settings influence conventions such as day and month order, decimal separators, and grouping separators. If values convert inconsistently, use Change Type > Using Locale and select the locale that matches the source data. Do not select a locale merely because it matches the computer’s settings if the file was produced using a different convention.
Null represents missing data in Power Query; it is not identical to zero or an empty text string. An error means Power Query could not complete an operation for that value, such as converting unrecognized text to a number. Replacing errors with zero may make a report appear complete while concealing bad input. Prefer to identify why the error occurred and decide whether to correct the source, exclude the row, or apply a documented replacement rule.
Power Query transformations versus worksheet formulas
Worksheet formulas calculate in cells and update when their referenced cells change. Power Query transformations prepare a dataset during a query refresh and return a result table. For a one-off calculation that users need to see and edit row by row, a worksheet formula may be more direct. For repeatable cleanup of imported data, Power Query often avoids maintaining long formula columns and repeated manual edits.
The two approaches can also work together. A query can load a clean table, and formulas or PivotTables can analyze that output. The query does not replace every Excel feature: it is not a live formula recalculating after every cell edit. To obtain updated source data and rerun its transformations, refresh the query. Also remember that manually typing into cells in a query output table is usually not a durable way to amend the data, because a refresh can replace the loaded results.
Refresh behavior and where results go
Power Query keeps its instructions separately from the source. The query can load results to a worksheet table, create a connection, or—in supported workflows—load to the Data Model. The destination affects how the result is used, but not the basic idea of the transformation steps. For an ordinary worksheet output, use Close & Load; to specify a destination or connection option, use Close & Load To.
Refreshing reruns the query and may update, add, or remove output rows according to the current source and query steps. A refresh can fail if a file has moved, a column has been renamed, credentials have expired, or the source is unavailable. If later data introduces a new format or unexpected value, an earlier type-conversion step may produce errors. A dependable process includes checking refresh status and reviewing the output when the source structure changes.
On Excel for Mac, Power Query is available in Microsoft 365, but supported sources and editing capabilities can differ by Excel version. Ribbon labels and some features may not match Windows exactly. Excel for the web also has more limited Power Query authoring support than the Windows desktop application; use the desktop editor when a needed transformation or connection option is unavailable.
Common problems and how to troubleshoot them
- A date or number becomes an error: Check the source convention and use Using Locale where needed. Inspect the original text for symbols, spaces, or mixed formats that prevent conversion.
- Values that look alike do not match: Apply Trim and, if appropriate, Clean. Check for inconsistent capitalization, punctuation, nonbreaking spaces, and different spellings before replacing values.
- Leading zeros disappear: Set an identifier column to Text rather than a numeric type. Identifiers are labels, not quantities, even when they contain only digits.
- A query breaks after a source change: Review Applied Steps from the top. A step that refers to a renamed or removed column can prevent later steps from working.
- New records are missing: Confirm that the source table or file includes them and that query filters do not exclude them. Then refresh the query and check whether the loaded output is the expected destination.
For troubleshooting, inspect the preview after each important step instead of waiting until the end. This makes it easier to identify the first operation that changes a value unexpectedly. It is also sensible to keep the source data intact and document any rule that changes categories, removes records, or substitutes missing values.
Frequently Asked Questions
Does Power Query change the original Excel data?
No. A query normally reads from its source and applies transformations to the query result. It does not write those changes back to the original range or external file. The cleaned output is loaded separately.
How do I change a value in Power Query?
Select the relevant column in Power Query Editor and use an appropriate command, such as Transform > Replace Values, a format command, or a data type conversion. The change is recorded as a step and reapplied on refresh.
Why does Power Query turn a date into an error?
The text may use a date convention or locale different from the one Power Query assumed, or the column may contain mixed or invalid entries. Use Change Type > Using Locale and inspect the rows that still fail.
What is the difference between null and blank?
Null indicates a missing value in Power Query. An empty text string is text with no visible characters, while zero is a numeric value. They can behave differently in filters and calculations, so choose a replacement only when it fits the data’s meaning.
Do I have to learn M to use Power Query?
No. The ribbon and menus cover many common transformations and generate M steps automatically. M is useful for inspecting or customizing a query, but it is optional for routine importing and cleanup.
How do I apply the same transformations to new data?
Keep the query connected to the recurring source, then use Data > Refresh All after the source is updated. The saved steps run again, provided the source remains accessible and its structure is compatible with the query.
Related
- Spreadsheet Free Budget Planner: A Simple Excel Setup
- Spreadsheet Budget: Build a Monthly Plan in Excel
- Excel Power Query: Combine Queries Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete Certain Data in Excel Without Losing MoreTo delete only selected data in Excel, select the cells and press Delete to clear their contents, or…
- Create Rules in Outlook to Move Emails AutomaticallyTo move Outlook emails automatically, create a rule that matches a condition—such as the sender or words in…
- How to Lock an Excel Page and Protect Its Cells SafelyTo lock an Excel page, protect the worksheet: in Excel for Microsoft 365 on Windows, open the sheet,…
- Hanging Indent in Word: A Step-by-Step GuideTo create a hanging indent in Microsoft Word, select the paragraph or paragraphs, open the Paragraph settings, and…
- How to Insert a Drop-Down Menu in Excel and Edit ItTo insert a drop-down menu in Excel for Microsoft 365 on Windows, select the cell, choose Data >…
- How to Protect an Excel Sheet Without Locking EverythingTo protect an Excel sheet in Microsoft 365 for Windows, unlock any cells people should be able to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.