Step 7 of 8 in Tech Lab
Next in your path: 7.2.3 Sheets Systems: connected tools and dashboards
Before this course: Excel for Analysis: PivotTables, lookups and data cleaning (course 7.1.2). You should already be able to clean data with TRIM, VALUE and DATEVALUE, use XLOOKUP and INDEX with MATCH, write UNIQUE, SORT and FILTER formulas and build PivotTables with grouped dates. In 7.1.2 you cleaned data by hand once. In this course you teach Excel to do the same cleaning every month, by itself.
A note on versions: Power Query is built into Excel for Windows (Microsoft 365 and recent versions), Excel for Microsoft 365 for Mac and Excel for the web. Excel 2016 and Excel 2019 for Mac do not have it. Macros (VBA) need the desktop app. Menu names can differ by version.
Think of a juice machine. You put fresh fruit in the top, the machine washes, peels and squeezes it, and juice comes out the bottom. You do not peel each orange by hand every morning. An automated workbook works the same way: new data goes in, the steps run, and the report comes out.
| Job | Best tool | Why |
|---|---|---|
| Import, clean and combine data every month | Power Query | Steps re-run on refresh |
| Summarise and filter results | PivotTables, slicers, timelines | Click to explore, no new formulas |
| Repeat clicks such as formatting a report or printing | Recorded macro (VBA) | Replays your actions |
| Automate in Excel for the web | Office Scripts | Runs in the browser (Lesson 7) |
A small shop exports one CSV file per month into a folder called Sales. Each file has the same five columns: OrderID, Date, Store, SKU, Qty. A second, small table called Products lists each SKU with its Name and Price. The goal is a report of sales by store and month that updates when a new month's file is added.
Videos by other creators on YouTube, for reference. They are not made by or affiliated with The Future Market.
[Host] "If you clean the same report by hand every month, this course is for you."
[Text on screen: Connect. Transform. Combine. Load.] "Power Query works in four phases. Connect to the data, transform it, combine it with other data, and load it into Excel."
[Screen: Data tab, Get & Transform Data group, Get Data button] "It lives on the Data tab. Every change you make is recorded as a step."
[Screen: new month's file dropped into a folder, Refresh All clicked] "Next month, drop the new file in the folder and press Refresh. All the steps run again by themselves."
List three tasks you repeat every week or month in Excel. For each one, write the tool you would use from the table (Power Query, PivotTable and slicer, recorded macro, or Office Scripts) and one sentence on why.
Copy this, open Claude, paste it, and Claude will walk you through the lesson step by step.
For a grown-up: Claude is for adults (18+). Copy this, paste it into Claude, and use the answer to explain the lesson to your child.
I'm learning "Excel Automation: Power Query, dashboards and macros" on The Future Market. Lesson 1: "Plan a workbook that refreshes itself". What the lesson teaches: - Power Query is Excel's tool for getting and shaping data. On Windows it sits in the Get & Transform Data group on the Data tab. Click Get Data to see the sources. - Microsoft describes four phases: Connect (link to files, databases, the web or tables in the workbook), Transform (remove columns, change data types, filter rows, without changing the original source), Combine (merge or append queries) and Load (put the result on a sheet or in the Data Model). - Power Query records each change as a step. When you refresh, it runs every recorded step again on the new data. That is the whole secret of automation. - The steps are written in a language called M in the background. You do not need to write M for this course. - Choose the right tool for each job: - Job - Best tool - Why - Import, clean and combine data every month - Power Query - Steps re-run on refresh The exercise I'm working on: List three tasks you repeat every week or month in Excel. For each one, write the tool you would use from the table (Power Query, PivotTable and slicer, recorded macro, or Office Scripts) and one sentence on why. I'm stuck. Please: 1. Explain the main idea in plain, simple words with one everyday example. 2. Walk me through the exercise one small step at a time. Ask me what I've done so far before giving the next step. 3. Don't just give me the final answer. Help me do it myself, and check my work when I share it.
I'm a parent helping my child (about 8 years old) with a lesson called "Plan a workbook that refreshes itself" from the course "Excel Automation: Power Query, dashboards and macros". Here's what the lesson says: Think of a juice machine. You put fresh fruit in the top, the machine washes, peels and squeezes it, and juice comes out the bottom. You do not peel each orange by hand every morning. An automated workbook works the same way: new data goes in, the steps run, and the report comes out. The activity: List three tasks you repeat every week or month in Excel. For each one, write the tool you would use from the table (Power Query, PivotTable and slicer, recorded macro, or Office Scripts) and one sentence on why. Please explain this so an 8-year-old understands: short sentences, a fun everyday example, and no hard words. Then give me 3 simple questions I can ask my child to check they understood, and a way to do the activity together, one tiny step at a time.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
What are Power Query's four phases?
Connect, Transform, Combine and Load.
What happens to your recorded steps when you refresh a query?
Power Query runs every step again on the new data.
How is Power Query's Trim different from Excel's TRIM function?
Power Query's Trim removes spaces only at the start and end; Excel's TRIM also removes extra spaces between words.
Why should you fix capital letters before removing duplicates in Power Query?
Because Power Query is case-sensitive, so "North" and "north" count as different.
What must be true about files you combine from a folder?
They sit in a dedicated folder with no extra files and have the same column names, types and number of columns (order can differ).
What appears when you append two tables and one has a column the other lacks?
Null (empty) values in that column for the rows that did not have it.
Which join kind keeps every order and adds matching prices?
Left outer.
Which join kind shows orders whose product code is missing from the Products table?
Left anti.
How do you make one slicer or timeline control two PivotTables?
Use Report Connections and tick both; they must share the same data source.
In which file type must you save a workbook that contains macros?
Excel Macro-Enabled Workbook (.xlsm).
What does Use Relative References change when you record a macro?
It records moves relative to the active cell instead of fixed cell addresses.
Why does every automated report need check cells such as "latest date"?
A failed Power Query refresh can keep old data without an error, and checks catch that.
This lesson is part of full access: every course and every level for one payment.
This lesson is part of full access: every course and every level for one payment.
Your notes stay on this device only. No account needed.