Clean and analyse data with an assistant

Get an assistant to clean and summarise a dataset, then catch its wrong assumptions with a cleaning report and a pivot table.

[[big 640 | late deliveries in August, from a delivery export of 4,012 rows]]

An assistant ran code on the file and came back with this figure, and a chart, within a minute. The figure is wrong, though nothing on the screen tells you so.

---

You set four rules for this export

You remove names and addresses, upload the export and ask the assistant to explain what it did.

Each Job ID counts once Skip cancelled jobs Late means delivered after the promised time Dates are day first, so 03/07 is 3 July

---

Three lines make **640 wrong**

[[doc notice | Rows in 4,012 | Duplicates removed 318 | Cancelled removed 41 | Blank Notes removed 205 (1) | Read month first 1,180 (2) | Undelivered, counted late 112 (3) | Rows out 3,448 | 1: Irrelevant to lateness. | 2: Yours are day first. | 3: Undelivered isn't late. | find 3 | case That's your rule]]

---

Ask for the cleaning report with your question

[[rewrite Which route had the most late jobs? | Which route had the most late jobs? List rows at start and end, what you removed and why, and your assumptions about blanks and dates.]]

---

A wrong filter shows **no error**

[[flow One easy figure, one that matters | Pivot the raw file, with your rules as filters | Match? Use it. Gap? Find the rule]]

The easy one could be one route in one week, and the one that matters is the route it calls worst.

---

?? Gemini has appeared in the sidebar of your work Sheets. Can you run the sales file through it? Yes, it's inside a work app, so IT must have approved it + Check your company's AI rules for that tool first Yes, as long as you only ask for a chart => Workspace admins can turn Gemini on or off in Sheets, and it's on by default. Seeing it there doesn't mean anyone approved it for work data. Check before you use it, and take out anything the question doesn't need.

---

[[order Your first run, in order | Pick a public table from data.gov.sg | Upload it with your rules for its columns | Ask for two figures, a chart and the report | Read the report against your rules | Check both figures in a pivot table]]

Upload a public table from data.gov.sg to an assistant and ask it for two summary figures, one chart and a report on how it cleaned the data. Then check both figures yourself with a pivot table in your spreadsheet.

Course

Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).