Excel Data Analysis Reporting with Power Query

From messy export to refreshable report in one day.

Every month the same export lands in your inbox: blank cells, names typed three different ways, dates stored as text and duplicate rows. Someone spends hours tidying it by hand, and next month they do it all again.

This practical one-day Excel data Analysis Reporting course teaches you to use the Power Query Editor in Microsoft Excel to clean, combine and reshape business data once, then refresh it in seconds whenever new data arrives. You then turn the clean output into clear, accurate reports with PivotTables, PivotCharts and slicers.

You learn by doing. Throughout the day you work on a realistic sales data set from a fictional UK wholesaler, fixing the kind of problems you meet in real company data, and you leave with a working, refreshable solution you can adapt for your own reports.

---

Why Power Query?

Power Query for data analysis reporting (shown in Excel as Get & Transform Data) connects to your data, cleans and reshapes it, and loads the result into Excel. It never changes your original data. Instead, it records each action as a step, like a recipe. When next month’s data arrives, you press Refresh and the whole recipe runs again.

• Save time: if one person spends half a day a month cleaning an export by hand, that is around 48 hours a year. A query built once removes most of that work.
• Reduce errors: the same steps run the same way every time, so nothing is missed or typed incorrectly.
• Trust your numbers: on the course, a single mis-typed product code hides a £6,407 order, about 8% of half-year sales. You learn how Power Query exposes problems like this before they reach a report.
• Show your working: every step is listed in order, giving managers and auditors a clear trail from source data to final figure.

---

Who should attend?

This data analysis reporting course is for anyone who regularly receives data from another system and has to turn it into a report, for example:

• Finance, credit control and management accounts teams
• Sales, operations and supply chain analysts
• Project and programme support officers (PMO) producing status and performance reports
• HR and payroll administrators working with system exports
• Managers who want reliable figures without rebuilding the same spreadsheet every month

---

What do you need for the course?

No previous Power Query or data analysis reporting experience is needed. However, you should be comfortable with everyday Excel: moving between sheets, sorting and filtering, and writing simple formulas such as SUM. Knowing what an Excel Table is (Ctrl+T) is helpful but we cover it on the day.

You need Excel for Microsoft 365, or Excel 2024, 2021, 2019 or 2016, on a Windows computer. Power Query is also available in Excel for Mac and Excel for the web, but some menus differ from those used on the course. If you are unsure which version you have, contact us before booking and we will help you check.

---

What will you leave with?

By the end of the data analysis reporting course you will have built a complete, refreshable Power Query solution in your own copy of the practice file:

• A Sales Orders query that turns 100 messy exported rows and 12 web orders into 108 clean, analysis-ready rows
• An Overdue Orders list for credit control that updates itself
• A Sales by Region summary built with Group By
• An Actual vs Target report comparing monthly sales with target for every region
• A PivotTable report with a PivotChart and slicers, built from the clean data
• A quick reference card of the 20 most useful Power Query commands
• A 30-day action plan to apply Power Query to a report you produce at work

---

What are the course objectives?

By the end of this course, you will be able to:

• Explain what Power Query does and its four stages: Connect, Transform, Combine and Load
• Open the Power Query Editor from an Excel table and find your way around its ribbon, Queries pane and Applied Steps
• Profile a data set to find blanks, errors and inconsistent values before you trust it
• Clean data using Fill Down, Split Column, Trim, text case and data types, including UK date formats
• Remove duplicates and resolve unmatched codes so your totals are accurate
• Combine tables with Append and Merge, and add custom and conditional columns such as Net Sales, VAT and Order Size
• Summarise with Group By and reshape with Unpivot to compare actual results against target
• Load and refresh your results, then present them with PivotTables, PivotCharts and slicers
• Judge whether an output can be trusted, using row counts and control totals

---

Frequently asked questions

Do I need to have used Power Query before?

No. The Data Analysis Reporting course starts from the beginning. You should already be comfortable with everyday Excel tasks.

Will this work on a Mac?

Power Query is available in Excel for Mac, but some menus and features differ from the Windows version we use. For the best experience, attend on a Windows PC with Excel for Microsoft 365 or Excel 2016 onwards.

Do I need to learn to write code?

No. Everything is done with menus and buttons. We show you the M code Power Query writes in the background so you know where to find it, but you do not need to write it.

Can I use my own data on the course?

The main exercises use the practice file so everyone can check their results. In the afternoon you plan a query for your own work. If you bring your own data, please remove or anonymise any personal or confidential information first.

What happens after the course?

You keep the workbook and practice file, so you can repeat the exercises and extension tasks. Your 30-day action plan helps you apply Power Query to a real report straight away.

---

Is this Excel data analysis reporting course for you?

---

Training Options

Virtual classroom using Microsoft Teams: £1,900+VAT for up to 8 delegates

On-site: £1,999+VAT for up to 8 delegates (rates may differ if outside Hampshire, Dorset and Wiltshire)

---

Microsoft Data Analysis resource