Excel Copilot builds Power Query steps from a prompt

Key insights

• A request to prepare the cinema data for a pivot table produced one query with multiple transformation steps.
• Copilot reconstructed full dates by combining day-and-month text with the year in the week-start date.
• The generated query selected columns by position, so changes to the source layout could affect the result.
• Copilot added a ticket-price lookup and calculated revenue even though the prompt did not request them.
• The demonstration used an Excel Beta Channel build; the transcript says the feature was not in regular Excel at that time.

Excel Copilot can turn a natural-language request into a Power Query query that reshapes messy data for analysis. This can help Excel users who know the outcome they need but want assistance building the transformation steps. Wyn Hopkins [MVP] demonstrates the workflow with a cinema ticket workbook and checks the generated query in a YouTube video.

What it does

Power Query connects to data and applies a sequence of steps to transform it. In this example, the source worksheet mixed cinema session details, ticket counts, repeated totals and date text without a year. The user asked Copilot to import the workbook data and reorganize it into a format ready for a pivot table.

Copilot created a query that removed unneeded columns, gave columns clearer names, reconstructed dates, filled dates down to the relevant rows, filtered for session times and unpivoted ticket-type columns. Unpivoting changes data arranged across several columns into rows, which can make the result easier to analyze. The output also included ticket prices and calculated revenue, beyond what the prompt requested.

The important point is that Copilot produced a sequence of Power Query steps, not just a cleaned, one-off table. Those steps can be inspected in the query editor. That makes it possible to check how the result was produced rather than treating the output as a black box.

Who it is for and what you need

This approach is aimed at people preparing data in Excel, including users who may not be comfortable building every Power Query step themselves. The example uses a workbook with a source worksheet and a separate ticket-pricing table. The request specifies the intended outcome—data ready for a pivot table—but does not spell out every transformation.

The transcript identifies the demonstration as running in an Excel Beta Channel build and says the capability was not in regular Excel at the time of recording. It does not identify a required license, a specific Excel version, a region, or the precise setting or menu path used to start the Copilot workflow. It also does not establish current availability, so check the Excel environment you use rather than assuming the feature is enabled.

The example requires a source workbook that Copilot can connect to. The presenter had to approve the connection and then confirm loading the result. A test with a SharePoint file was not completed: the presenter reported a separate connection issue and did not verify that source type in this demonstration.

How it works

1. Provide Copilot with the workbook data and describe the result you want. In the example, the instruction was to import the data and reorganize it into a pivot-table-ready format.
2. Allow the connection when prompted. Copilot processes the request and builds the query; the presenter says this took several minutes in the test.
3. Review the loaded result, then open the query for inspection. In the demonstration, the presenter uses Query, Edit to open the Power Query editor.
4. Check the source selection and each transformation step. The query used the worksheet named Sheet1 and also accessed the ticket-pricing table.
5. Validate the output before relying on it. Check dates, session rows, ticket categories, prices and any calculated revenue against the original data and the intended requirements.

The date logic illustrates both the value and the need for review. The source contained entries such as “5th of Jan” without a year, while the week-start information supplied 2026. The query combined the text date with the year from the week-start date, then filled the resulting date down to the related session rows.

Some implementation choices may be fragile if the source changes. The query referred to columns by their positions and hard-coded names for ticket categories. If columns move or category names change, those assumptions may no longer match the workbook. The presenter also noted that the query separated the transformations into understandable steps, making the logic easier to inspect.

Limitations and open questions

The extra price lookup and revenue calculation are useful examples of why a correct-looking result is not automatically the right result. Copilot added both without being asked. A user may welcome the additional calculation, but it should be checked against the business requirement and source data before use.

The test does not establish how consistently Copilot handles other workbooks, complex transformations or changing source layouts. It also does not show a completed SharePoint connection test. Licensing, broader rollout and the exact controls available to users are not stated in the demonstration.

For a safer workflow, treat the generated query as a first draft: make the desired output explicit, inspect the steps, and compare the loaded result with known values. Users who already build queries manually can still use the generated steps as a starting point, but the video does not compare this approach with a manually built query or establish that Copilot can edit every existing query.

Sources

• Wyn Hopkins [MVP] Power Query demonstration in Excel

Frequently asked questions

What did Excel Copilot do to the messy Power Query data?

In the example, it selected and renamed columns, reconstructed dates, filled dates down, filtered session rows, unpivoted ticket counts, and added a price lookup and revenue calculation.

Was Copilot for Power Query available in regular Excel?

The presenter said the demonstrated capability was in an Excel Beta Channel build and was not in regular Excel at the time. The transcript does not establish current availability.

Can I review the Power Query steps Copilot creates?

Yes. The demonstration opens the query in the Power Query editor and inspects its steps. The presenter used Query, Edit.

Did the demonstration confirm that Copilot works with SharePoint files?

No. The presenter wanted to test a SharePoint file but could not connect, citing a separate connection issue. The test therefore did not verify that workflow.