AI and Power Query: What It Can and Cannot Do in Excel

AI can help you write, explain, and troubleshoot Power Query steps, but Power Query itself is not an AI assistant. In Excel for Microsoft 365 on Windows, you can use an AI tool to draft Power Query M code, then review and run that code in the Power Query Editor; connecting a query to an AI service is a separate, more advanced task.

What Power Query does—and where AI fits

Power Query is Microsoft’s data import and transformation tool. It connects to sources such as Excel tables, text files, databases, and some online services, then records steps for cleaning and reshaping data. You can refresh the query later to repeat those steps on updated source data. In Excel, you open it from the Data tab, commonly by choosing Get Data or selecting a table and choosing From Table/Range.

Power Query uses a language called M behind the scenes. The editor’s buttons generate M code as you filter rows, change data types, split columns, merge tables, or remove duplicates. The result is a repeatable data-preparation process—not a model that understands the meaning of your data or makes intelligent decisions by itself.

AI can be useful around that process. For example, you can describe a transformation in ordinary language and ask an AI assistant to suggest the corresponding M steps. You can also paste an error message and ask what it means. The AI supplies a suggestion; Excel executes the query. That distinction matters because a plausible-looking answer can still use the wrong column, make an unsafe assumption, or produce M code that fails.

What AI can help you do

For routine work, AI is most helpful as a coding and explanation assistant. It can turn a clearly stated requirement into a starting point, explain unfamiliar M expressions, or suggest ways to make a sequence of steps easier to maintain. It can also help you think through the order of operations—for instance, filtering out irrelevant records before expanding a large nested table.

Draft transformations: Ask for steps to trim whitespace, standardize text, split a combined field, or keep only rows matching a condition.
Explain existing queries: Provide a small, non-sensitive excerpt of M code and ask what each step does.
Diagnose errors: Share the exact error text and the relevant step, while removing private data and credentials.
Suggest a design: Ask how to combine monthly files with consistent layouts or how to handle missing values.
Create test cases: Ask what edge cases to check, such as blank cells, unexpected dates, duplicate IDs, or changed column names.

Suppose a sales table has a Region column with inconsistent capitalization and extra spaces. An AI assistant might suggest trimming the text and converting it to uppercase. That is a concrete, testable transformation. It should not be treated as evidence that “north ” and “North” represent the same business category unless that is true in your data.

How to use AI-generated M code in Excel

Start with a copy of the workbook or a small sample, especially if the query feeds reports used by other people. In Excel for Microsoft 365 on Windows, select the source table and choose Data > From Table/Range, or use Data > Get Data to connect to another source. This opens the Power Query Editor, where you can make transformations through the interface or inspect the generated code.

Describe the data structure. Tell the assistant the column names, relevant data types, desired result, and any rules for blanks or unusual values. Use sample rows with fictional or anonymized data if needed.
Ask for Power Query M, not an Excel formula. State whether you need a complete query or just a step to add after a named step. M and worksheet formulas are different languages.
Review the suggestion before using it. Check every table name, column name, step reference, and assumption. A code answer written for a different source structure will not automatically adapt to your workbook.
Try it in the editor. In Power Query Editor, select Home > Advanced Editor to inspect or edit the query. If you are adding a step, make sure it refers to the correct preceding step. You can also use the interface to create the transformation and then inspect the M it produces.
Check the output and refresh behavior. Confirm row counts, data types, and representative values. Refresh the query after changing the source or testing with new files.

For a simple cleanup, an assistant could propose a transformation that trims whitespace in a text column. Before accepting it, verify that the column really contains text and that trimming will not alter meaningful values. For instance, leading or trailing spaces are usually accidental in a region label, but spaces can be significant in some identifiers or fixed-width codes.

Excel for Mac also has Power Query capabilities in Microsoft 365, but connector support and some authoring features can differ from Windows and may depend on the Excel build. The Windows ribbon paths above may not match your Mac interface exactly. If a command or connector is missing, check Microsoft’s current Excel for Mac documentation rather than assuming the Windows steps are available.

Is Copilot built into Power Query?

Not in the sense of a universal AI chat panel inside every Excel Power Query Editor. Microsoft 365 Copilot features in Excel can help with certain workbook tasks, but availability depends on the product, license, organizational settings, and the feature rollout. Copilot’s presence in Excel should not be taken to mean that it can automatically author, validate, or maintain every Power Query transformation.

Power Query is also used in products beyond Excel, including Power BI and Microsoft’s data-integration services. AI-related features available in one Microsoft product or service do not necessarily exist in Excel’s Power Query Editor. Check the documentation for the specific product and feature you plan to use, and confirm that it works with your data source and licensing arrangement.

If your goal is simply to clean a table, the built-in Power Query interface is often the most direct option. If your goal is to ask questions about data in a workbook, an available Copilot feature may be more relevant. If you need a custom AI classification or text-generation step in a refreshable pipeline, that is a different design problem: it may require an external service, an API, and careful security and maintenance planning.

Can Power Query call an AI service?

In some workflows, Power Query can send requests to a web service, including an AI service that offers an API. This is not the same as turning on an AI setting in Excel. It involves constructing requests, supplying authentication, handling the service’s response, and respecting the provider’s rules and limits. The exact approach depends on the API and the environment where the query runs.

A typical use might be sending a short product description to a classification service and receiving a category that Power Query expands into a column. Before building this, decide whether the task truly needs AI. If a stable rule—such as mapping known codes with a lookup table—solves it, a deterministic transformation is usually easier to audit and refresh.

API-based workflows also introduce operational concerns. A query may fail when a service is unavailable, changes its response format, limits requests, or changes its pricing. Some providers return results that vary between calls, so refreshing the same source may not always produce identical classifications. You may need error handling, logging, review of uncertain outputs, and a plan for maintaining credentials.

Privacy, security, and reliability

Do not paste confidential workbook contents into a public AI chatbot unless your organization has approved that tool and its data-handling terms. The same caution applies when a query sends information to an external API: the data may leave your organization’s environment. Follow your employer’s rules for personal information, customer records, regulated data, and intellectual property.

Keep API keys and passwords out of query text, shared workbooks, and screenshots. Use the authentication and credential-management options supported by the particular connector and service, and ask an administrator about approved storage where appropriate. Also consider who can refresh a query: a connection that works under one person’s account may fail for another user or on a shared machine.

AI output needs ordinary data-quality checks. Compare a sample of results with known answers, inspect how blanks and unusual inputs are handled, and make sure the query preserves the expected number of records. For high-impact classifications, have a person review uncertain or consequential results. Do not assume that a successful refresh means the answer is correct.

Common problems and how to avoid them

One frequent problem is asking for code without describing the query context. M steps refer to earlier steps by name, and a column that looks obvious to a person may be called something different in the actual table. Include exact column names and say whether the query begins from an Excel table, a folder, or another source.

“The column wasn’t found”: Check spelling, spaces, punctuation, and whether an earlier step renamed or removed the column.
Type conversion errors: Confirm that values really match the requested type. A date column containing labels such as “unknown” may need a defined way to handle those rows.
Unexpected row loss: Review filters, joins, and duplicate-removal steps. A transformation can run successfully while excluding records you intended to keep.
Code refers to the wrong step: In Advanced Editor, inspect the step sequence and make sure each new expression uses the intended prior step.
Refresh works only for one person: Check credentials, privacy settings, source paths, and whether the other user has access to the same file or service.

When an AI suggestion fails, provide the exact error and the smallest relevant code excerpt, not the entire workbook. Ask for an explanation of the failure and a revised step that matches the actual column names. Then test the revision on sample data. This is safer and more useful than repeatedly asking for a complete replacement query.

When to use AI, Power Query, or something else

Use Power Query by itself when the task is a repeatable, rule-based transformation: combining files, removing blank rows, splitting columns, changing types, or joining tables. These steps are visible, refreshable, and easier to audit than ad hoc manual edits. Use AI as a helper when you need to draft or understand M, or when the classification task genuinely depends on interpreting text that does not follow stable rules.

Consider a normal Excel formula for a small calculation that belongs in the worksheet and should update cell by cell. Consider a pivot table or other Excel analysis feature when the goal is summarizing data rather than reshaping it for reuse. For large-scale, shared data pipelines, Power BI, Microsoft Fabric, databases, or a managed integration platform may be more suitable, depending on your organization’s tools and skills.

A practical decision test is to ask whether the same input should always produce the same output. If yes, use a clear rule or lookup where possible. If the task requires interpreting ambiguous language, AI may help, but define how to check the result and what happens when the model is uncertain. The more consequential the output, the stronger the need for review and traceability.

Frequently Asked Questions

Does Excel Power Query have AI?
Power Query is a data import and transformation tool, not an AI model. AI capabilities may be available elsewhere in Microsoft products or through external services, but they are not automatically part of every Excel Power Query Editor.

Can ChatGPT write Power Query code?
An AI chatbot can suggest M code and explain code you provide. Treat the result as a draft: check it against your actual source, column names, and requirements, then test it in the Power Query Editor.

Can Power Query use ChatGPT or another AI API?
It can be possible to call a web API from a query, depending on the service and your environment. You must handle authentication, privacy, response formats, errors, request limits, and any applicable costs.

Is Copilot the same as AI in Power Query?
No. Copilot features in Excel or other Microsoft products are product-specific and may have separate availability and licensing. Their presence does not mean every Power Query transformation can be created or maintained by Copilot.

Should I use AI to clean spreadsheet data?
For consistent tasks such as trimming spaces or changing a type, ordinary Power Query steps are usually more predictable. AI can help with ambiguous text, but verify its output and avoid sending sensitive data to an unapproved service.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.