# Build a refreshable Power Query with Copilot Get Data, then prove next week's file still works

> In Excel for the web, invoke Copilot's Get Data skill with explicit business rules, confirm it created a real Power Query (not pasted values), check rows and sums against the source, inspect the Advanced Editor for hardcoded columns, then refresh with next week's file before you trust it.

- **Category:** [Excel](https://stepgleam.com/categories/excel)
- **Difficulty:** Intermediate
- **Time:** ~40 minutes
- **Tools:** Microsoft Copilot in Excel, Power Query, Excel for the web
- **Reliability score:** 88/100
- **Steps:** 5
- **Last updated:** 2026-11-01
- **Published:** 2026-11-01

## Tools used

- [Microsoft Copilot in Excel](https://support.microsoft.com/excel) — Hosts the built-in Get Data skill (@get-data) that drafts the Power Query
- [Power Query](https://support.microsoft.com/en-us/office/about-power-query-in-excel) — Stores the reviewed M steps you refresh next week
- [Excel for the web](https://www.microsoft.com/microsoft-365/excel) — Where Microsoft listed the mid-October 2026 Get Data rollout

## What you'll get

A reviewed Power Query in Queries & Connections that matches source row counts and totals, survives a refresh on next week's file, and has no silent hardcoded column list you did not accept.

## Who this is for

Excel users on Excel for the web with Copilot who repeat the same import/cleanup every week or month and want a query they can refresh instead of a fresh chat.

## Before you start

Microsoft rolled Copilot's built-in Get Data skill into Excel for the web in mid-October 2026: you describe the data and transformations in natural language, and Copilot builds a Power Query you can inspect and refresh. George Mount (Stringfest Analytics, 22 Oct 2026) argues the point of Get Data is not another chat you re-run every month — it is a reviewed query you keep. InflueXcel put @get-data through three real traps (US dates, folder+subfolder, blocky goals) and a "next week" schema change. This guide follows those two human write-ups plus Microsoft's announcement wording. Exact AppSource/license gates and every supported source stay gaps.

## Steps

### Step 1: Confirm Get Data is available, then ask for a query — not a one-off paste

**Tool:** Microsoft Copilot in Excel

Microsoft's October 2026 Excel notes and Neowin's write-up of the AI-at-Work roadmap: Copilot in **Excel for the web** gets a built-in **Get Data** skill. You describe the data you need and how to shape it; Copilot connects, transforms, loads into the sheet, and leaves a **Power Query script** you can open in the Advanced Editor. Mount: treat the skill as the playbook and your prompt as this month's assignment — the durable artifact is the saved query, not the chat. Availability may still vary by tenant; if you do not see Get Data / `@get-data`, stop here (gap).

**Why this works:** Without a saved query you are back to hoping next month's chat remembers the rules.

**Prompt:**

```
Import this month's sales export. Keep account codes as text, standardize the dates, retain all transactions, join the account reference table, and flag unmatched codes. Build a Power Query query I can review and refresh.
```

[Open step 1](https://stepgleam.com/guides/build-a-refreshable-power-query-with-copilot-get-data#step-1)

### Step 2: Grant access, load only after Copilot asks, then prove it created a real query

**Tool:** Microsoft Copilot in Excel

InflueXcel's first live test (web orders CSV with US dates like `1/13/2027`): invoke with `@get-data`, ask for a table on a new sheet with correct types. Copilot asked permission to read the file and asked again before loading; it dropped a nameless index column on its own and used `en-US` locale so `1/13/2027` became 13/01/2027. Their first of five checks: confirm **Queries & Connections** shows a query — not values pasted into cells. They verified **612 rows** and sum **2.163.477,50** against the file.

**Why this works:** A pretty table of values has no refresh button next Monday.

[Open step 2](https://stepgleam.com/guides/build-a-refreshable-power-query-with-copilot-get-data#step-2)

### Step 3: Spell joins and exceptions the way Mount does for append / unpivot / merge

**Tool:** Power Query

Mount's three refresh-worthy patterns when you brief Get Data: **append** recurring exports (combined rows should equal the sum of sources before intentional filters); **unpivot** wide category/month columns into fields you can Pivot; **merge** reference tables with an explicit left outer join when every transaction must survive, and **flag unmatched codes** instead of letting an inner join drop sales. He wants those decisions saved in the query, not re-decided by the model next month.

**Why this works:** Vague \"clean this\" leaves Copilot free to change which invoices survive between runs.

[Open step 3](https://stepgleam.com/guides/build-a-refreshable-power-query-with-copilot-get-data#step-3)

### Step 4: Open Advanced Editor and hunt for hardcoded columns, paths, and skip-N rows

**Tool:** Power Query

InflueXcel's folder test (one CSV per month plus a trap `antiguos` subfolder): Copilot filtered the subfolder path and converted text amounts — row/sum matched — but some steps **fixed the column list**. Their goals/blocks case also over-used fixed values even when header detection by content was correct. Check four: look in Advanced Editor for hardcoded paths, years, column name lists, and \"skip N rows\". Those are the bugs that only appear when next week's file changes.

**Why this works:** Hardcoded column lists pass today and break the Monday refresh.

[Open step 4](https://stepgleam.com/guides/build-a-refreshable-power-query-with-copilot-get-data#step-4)

### Step 5: Refresh on next week's file; if it fails, expect a new query — delete the old one yourself

**Tool:** Power Query

InflueXcel's decisive test: next week's orders CSV dropped `coupon` and added `gift_wrap`. **Refresh All** failed with `The column 'coupon' of the table wasn't found` because the type-change step had handwritten the column list. Asking Copilot to fix it: it diagnosed the step correctly but **could not edit the existing query** — it created a new corrected one and left the broken query in the workbook for the human to remove. Their fifth check is mandatory: does refresh survive next week's schema?

**Why this works:** Passing today's file is not the same as surviving next week's export.

[Open step 5](https://stepgleam.com/guides/build-a-refreshable-power-query-with-copilot-get-data#step-5)

## Pro tips

- Spell the business rules in the prompt: keep codes as text, which join, what to flag — Mount's example does this on purpose.
- Before you trust it, run InflueXcel's five checks: real query vs values, rows+sum vs source, types, hardcoded bits in Advanced Editor, refresh on next week's data.

## Common mistakes

- Re-asking Copilot every month instead of refreshing the reviewed query — Mount compares that to putting RANDBETWEEN in charge of which invoices survive.
- Assuming Copilot can edit the broken query in place; InflueXcel found it creates a new corrected query and leaves the failing one for you to remove.

## FAQ

### Is Get Data the same as Clean Data?

Mount: Get Data brings in and transforms data; Clean Data flags issues in data already in the workbook. Different built-in skills.

### Do I need Excel desktop?

Microsoft's October 2026 roadmap/announcement scoped the Get Data skill rollout to Excel for the web. Availability still varies by tenant.

---

Canonical guide: https://stepgleam.com/guides/build-a-refreshable-power-query-with-copilot-get-data
Read this guide as Markdown: https://stepgleam.com/guides/build-a-refreshable-power-query-with-copilot-get-data.md