One-day workshop: Click-and-clean data in Excel Power Query

This workshop is for teams and organizations only at this time. I am currently not offering it to individuals. If you someone who could benefit from this service, check out my referral program.

Download a PDF brochure for this workshop:

Are you and your team are constantly copy-pasting data, dragging formulas over and around, fixing broken workflows and having just a rotten time in Excel, week after week?

Maybe you’ve thought of spending thousands of dollars in licenses for an alleged “spreadsheet killer.” Or perhaps you’re resigned to messy ad-hoc data cleaning as just the way things are, and there’s no way to improve it.

While it’s true that to work with data is to clean data, let’s at least make it a little more enjoyable and reliable, yeah? And we can do that without expensive software, and right from Excel, with just a few hours of training.

In this one-day workshop, you’ll learn how to set-and-forget your Excel data cleaning workflows, being able to re-run them with the click of a mouse.

What is Power Query?

Power Query is a data transformation tool from Microsoft which includes a graphical interface for connecting to and cleaning data:

• It allows you to read in data from a variety of sources such as databases, websites and more (in addition to Excel workbooks)
• Using a drag-and-drop editor, you can perform sorts, filters, joins and other common data cleaning techniques that might have required complex formulas, VBA scripts or manual manipulation previously
• Power Query is also used in Power BI, so if your team uses that tool too these skills will easily transfer there as well.

This is arguably the most important new feature in Excel in the last 20 years, but not everyone is using it! This will make a huge difference to your team’s productivity and accuracy with data reporting and analysis.

Workshop benefits

As part of this workshop, your team will be able to:

• Structure data in Excel in a way that maximizes efficiency and minimizes error
• Connect to Excel workbooks, CSV files and other data sources without file conversions or other workarounds
• Defeat Excel’s alleged “million-row limit” for working with data
• Build fully repeatable data cleaning processes that are refreshed and updated with no hassle

What makes this workshop different

I know there are lots of places to learn Excel these days. What distinguishes this workshop is that it’s:

• Participative. The quicker you apply what you learn, the more likely you are to retain it. This is literally a hands-on workshop where attendees will have plenty of time on the keyboard to try the concepts out for themselves with demos, activities and more.
• Authoritative. The workshop is designed and led by me, George Mount. I run my own analytics consultancy and am one of the few authors to have published on Excel with O’Reilly Media, the leading technical book publisher. This is not my “first rodeo” when it comes to data wrangling.
• Topical. No fake, boring data sources and examples here. Upon request, we will design the workshop to use data from your own organization or industry. In any case, we’ll use a variety of real world and fun datasets.

Due to their hands-on and participative nature, sessions work best for groups of up to 20 in-person or 35 online participants.

Who should attend

Really, anyone who wants to end poorly-designed Excel workbooks once and for all and make the most of their data. Typical job titles who could particularly benefit include:

• Data analysts
• Financial analysts
• Marketing coordinators
• Accountants
• Controllers
• Operations analysts

Ideally, attendees will have some familiarity with intermediate Excel topics such as PivotTables or lookup functions such as VLOOKUP() or XLOOKUP(). That said, we have had success with learners possessing skills as early as basic formulas and functions.

There is no coding experience required to attend this workshop. While Power Query can be coded, most everyday tasks such as those performed in this workshop don’t need to be.

What you’ll get

• A full day of either onsite or online instruction (about six hours total, plus time for lunch and breaks)
• Step-by-step demo notes explaining the entire course’s content
• Supplemental video content to practice with and share later
• Lifetime access to all slides, datasets and handouts used in the course

Workshop highlights

This is an example of what might be covered in the workshop. Each section includes both instructor demonstrations and time for learner exercises.

• Power Query as Excel’s ETL tool: What is “extract, transform, load” anyway? We’ll look at how this process works in Power Query and why it matters, along with taking some first steps for profiling and cleaning the data.
• What makes data “clean?” Gain a sixth sense for sniffing out bad data in this lesson. We’ll use some basic principles of how data should be structured to know what the data should look like before we even get started.
• Operating on rows: Here we’ll focus on those tasks you know and love with rows: sorting, filtering, recoding, and more. We’ll also see how Power Query works a little more like a database than a spreadsheet when it comes to missing values.
• Operating on columns. Ditto for the rows section, but here we’ll focus on columns: splitting, concatenating, creating calculated columns and so forth. At this point, we’ll also start working with Power Query on data from multiple file sources.
• XLOOKUP() to JOIN: If XLOOKUP() is duct tape, then JOIN is like a welder. You will learn how to combine data from multiple sources, using a variety of join types.

About your instructor

George Mount is the founder of Stringfest Analytics. He has over 10 years of experience in data analytics and holds master’s degrees in both finance and information systems from Case Western Reserve University. George is widely recognized an expert in the fields of data analysis and Microsoft Excel and is the author of Advancing into Analytics: From Excel to Python and R (O’Reilly Media, 2021).

George has extensive experience in reporting, analytics, and modeling and is a respected trainer in these fields. He provides training for companies throughout North America and beyond.

Software Required

For best results, please have the desktop version of Excel for Windows 365 installed. Earlier versions of Excel or versions for Mac may not track well with everything in the course.

Get started

Ready for your team to make data cleaning work for you? Drop me a line below and I’ll be in touch shortly to get started.

Please only use this form if you are looking to present this workshop for your corporate team. I am currently not offering this to individuals.

I look forward to teaming up with you for this workshop.