Power Query - Simply Explained

0:00 The tool that cleans your messy spreadsheets is already sitting inside Excel. It's called Power Query, and it's the data cleaning engine built into the Microsoft tools you probably open every day. Every click you make becomes a recorded step. So the same cleanup runs again next month without you. Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. You might assume a tool like this needs code, but you can build the whole thing with buttons. By the end, you'll know what Power Query is, how it works, and the few habits that keep it fast. What Power Query actually is. Power query is a data transformation and data preparation engine, and that phrase sounds heavier than it needs to. In plain terms, it pulls data in, reshapes that data, and puts the finished results somewhere useful.

0:43 Microsoft calls that extract, transform, and load and Power Query handles all three pieces. A transformation is any change you make along the way. You might remove a column, fix a spelling, or filter out rows you don't want. It's just a change and Power Query keeps track of every one. What surprises people is where it lives. Power query isn't a separate app you go and download because it's an engine built into tools you already open in Excel. You'll find it on the data tab under Get and Transform, and in power BI. It's the step that runs before your report exists. Same engine, two front doors. If you've ever clicked that data tab and wondered what get and transform did, you were looking straight at it. It's been sitting there all along. You also don't need to write a single line of code when you connect to a source and click a transformation.

1:23 Power query writes the code for you in the background. That choice matters because it opens the tool to people who've never programmed anything. You can start today without learning a language first. The scale underneath is bigger than the friendly button suggest. Power query can connect to hundreds of data sources, from a plain text file to a SQL server database, and it covers more than three hundred and fifty kinds of transformations across those sources. The library is huge because Microsoft keeps adding connectors for the services people actually use. From SharePoint lists to cloud warehouses. You won't use most of them, and you'll probably lean on about ten. Ten is plenty. A real example makes this concrete. Say every month you get a sales export where the header row sits on line for two columns need renaming and the dates arrive as text.

2:04 In Power Query, you point at the file. Fix those three things once and the next export cleans itself. When you refresh. Over the next few minutes, you'll see what those transformations look like in practice. The steps that record them, the language hiding underneath, how to combine tables, why data types cause so many errors, and the mistakes that slow beginners down where you'll run into it. Power query shows up in more places than most people realize in Excel and Power BI desktop. It runs as a desktop editor built into the app in browser based products. The same editor runs online, which is how it powers data flows in power BI and in Microsoft Fabric. That spread is the reason the skill is worth your time. Microsoft puts Power Query inside Excel power BI Power apps, Power Automate, Azure Data Factory, SQL Server Integration Services, and Dynamics three hundred sixty five Customer Insights, among other products.

2:50 Learn the editor once, and you can shape data in any of them so the skill travels with you. The online version adds a couple of extras, like indicators that show which of your steps can be pushed back to the source? Those indicators don't exist in the desktop version. The buttons and the ideas stay the same either way, so nothing you learn in Excel gets thrown away when you open power BI for the first time. But knowing where it lives is only half the story. The reason anyone opens Power Query at all is a very specific kind of pain. The problem it solves. Picture the last time you cleaned a spreadsheet by hand. You exported a file. Deleted the same junk rows at the top, rename the same two columns, fix the same date format and pasted it into a report. Then you did it all again the following month and the month after that. The work feels productive because you're busy and the report does get finished, so nobody stops to ask what it cost.

3:34 Add up the manual steps across a year and the same spreadsheet can quietly eat days of your time. That adds up fast. When that report pulls from four different files, the manual version means four rounds of the same. Clicking. None of it is hard. It's just relentless. You might think the problem here is messy data, and that the answer is to chase down cleaner files. The real problem is that your clean up can't run itself again, so every new file drags you back to the same manual steps. Manual work also invites mistakes the tenth time you delete the same rows, your attention slips and nobody spots the missing line until a number looks wrong. In a meeting, Power query records each transformation as a step, and it applies those steps. When the query runs. It doesn't change the source data either, so the original file stays exactly as it was, and you're only shaping your own copy of it.

4:16 That recorded process is the whole point of the tool. When new data arrives, you refresh and Power Query brings in the additions, the changes, and deletions from the source. Each saved step runs automatically without you touching a single cell, so you clean the data once and then refresh forever, which turns the task you repeat into a process that repeats for you the hours you used to lose to the monthly ritual land somewhere more useful. The four phases Microsoft breaks Power Query in Excel into four phases, and the four words are worth remembering because together they describe the entire journey. Their connect, transform, combine, and load. Connect is where you point Power Query at your data, whether it sits in the cloud, on a service, or in a file on your own machine, the connection doesn't change anything at the source.

4:57 So you're always working with a view of the data rather than a replacement for it. The first time you do this, you pick a connector, point it at a location, and sign in. If the source asks you to transform is the shaping phase, and shaping covers everything from removing a column to changing a data type or filtering out rows. This is where most of your time goes, and it's also the phase power query makes easiest because each change is a click instead of a script. Combine is the joining phase, and it brings data from more than one source together. That's how you stitch a sales file to a customer list without copying and pasting between them. And it turns separate tables into one useful picture. Load is the finish line. You close the editor and load the result into a worksheet or into the data model, and from then on you refresh it whenever the underlying data moves.

5:37 You decide where it lands and you can change that later without rebuilding the query. For verbs one flow and once you can picture the flow, the editor stops feeling like a maze. Keep one thing in mind as we go, because every click you make is quietly recorded, and one of those recorded steps decides how fast your query runs. We'll come back to that. Applied steps. Open the Power Query editor and you'll see a pain on the right called Applied Steps. And it's the single most reassuring thing in the entire tool. Every transformation you make shows up there as its own name. Step in the exact order you made it. It's all there. Select any step and the preview jumps back to show you how the data looked at that point. That means you can rewind, inspect, and check your work without starting over, which is a real luxury when you're not sure where things went wrong.

6:20 In the online editor, a diagram view also draws your steps as connected shapes so you can see the flow at a glance. You can also fix things in place. Right click a step and you can rename it, delete it, drag it to a new position, insert a new step after it, or delete it along with everything that came after. A beginner who breaks a query usually just deletes the bad step and carries on the step. Names matter more than they look because they're the labels you'll read months later when you've forgotten what you did. Renaming a step from something like change type to a plain description costs you five seconds and saves you an hour. Microsoft describes the script behind all of this as a recipe that describes how to prepare your data and the applied steps. Pain Is that recipe shown as a friendly list? The list is the code and the code is the list. That's the real difference between a manual cleanup and a power query.

7:02 The manual cleanup lives in your memory and disappears when you close the file, while the recorded one waits for you and runs again on demand. The M language underneath those buttons. Power Query runs on a scripting language called the Power Query Formula language, or just M for short. Anything that happens in a query ends up written in M, and you can see all of it by opening the advanced editor. You don't have to write M, and you can use Power Query well without learning it. The editor writes it for you as you click, which means the code is a record of what you did rather than homework you have to do first. That's the deal. It still helps to know the shape of it. M is case sensitive, and a query is a series of named steps wrapped in a let expression that ends with an in statement. That's a formal way of saying each step builds on the one before it, and the last step is the result you asked for.

7:46 A single click becomes a single readable line when you capitalize a column. For example, Power Query writes one function call that does the work, and you can read that line almost like a sentence. You never have to guess what a click actually did, because the answer is always one tab away. The advanced editor is there for the moments when the buttons can't do what you need, and it's where you go to fine tune. For most beginners, though, it stays closed and that's completely fine. The language is a safety net, not a test. Merging and appending. Once your individual tables are clean, the real work is bringing them together and you have two ways to do it. They sound similar and behave nothing alike, so the gap is worth a minute. Merge joins two tables based on matching values in one or more columns, and it adds columns from the second table onto the first. It works like a database join and you choose the join kind, such as an inner join, which keeps only the rows that match on both sides.

8:32 A typical use is pulling a customer name and email onto a table of orders that only lists a customer ID. Append stacks rows. Instead, it creates a new query that contains every row from the first table, followed by every row from the second. So the columns line up and the table simply gets longer. If a new sales file lands every month with the same columns, append is how you stack a year of them into one table. The simplest way to hold on to the difference is this merge makes your table wider and append makes it longer reach for merge. When two tables share a key and you want related details side by side and reach for append. When you have the same columns across different months, regions or files. Both operations work on any query with a table shape and neither one cares where the data came from.

9:12 You can merge a spreadsheet with a database table as easily as two spreadsheets, which matters when your data is scattered across different systems. Data types and locale. Data types are the quiet source of most Power Query errors, and they're easier to understand than the name suggests. A data type is just the category A column belongs to, whether that's text, a whole number, a decimal number, a date, a date and time, a percentage, or a simple true or false value. Getting the type right matters more than it looks. Power query gives you type specific transformations and filters, and if a column has no type set, those options sit grayed out because the tool doesn't know what kind of values it's holding. Power query also includes profiling tools that show column quality and how values are distributed, which is a fast way to spot trouble before you build anything on top of the data, there's a catch all type called any.

9:56 Which is what a column gets when it has no explicit type. Microsoft recommends that you avoid leaving any columns in your finished query, because they push the guessing game downstream to whatever consumes your data. Watch for it. Power query tries to help by detecting types automatically against the database. It reads the schema and knows the types already, but against an Excel CSV or text file, it guesses by looking at the values. By default, it inspects the first two hundred rows and decides from there that guessing is where the classic error hides. Now this is where it gets interesting, because a date like the second of March can be written as day first or month first. And if your locale disagrees with the file telling Power Query the column is a date produces errors instead of dates. The fix is to right click the column, choose change type, and pick using locale so you can tell it exactly how the file writes its dates.

10:42 It's a small setting that saves you an afternoon of staring at a column of read error values. Refresh. Refresh is where Power Query earns its keep, and it's the payoff for recording all those steps in Excel. Once you've imported your data, you can refresh it to pull in additions, changes, and deletions from the original source. That's the reward. When you refresh, the saved sequence runs again automatically and in order, which replaces the routine of reconnecting and reshaping the data by hand. The query you built last month still knows exactly what to do this month. The options depend on where you're running Power Query. You can refresh manually whenever you want, or on a schedule in products such as power BI or programmatically through Excel's object model. If you're the automating type one honest note because it trips people up. Scheduled refresh is a power BI and cloud capability, and plain Excel doesn't have the same scheduling switch.

11:26 So if you're staying in a desktop workbook, you'll refresh it yourself. That's still a big shift. Your cleanup stops being a task you do and becomes something that runs on its own. And the gap between those two things is most of your time. The moment you hit refresh and watch a fresh month of data drop into a finished report, the idea clicks. You didn't clean anything, and the result is already in place. Where beginners go wrong. The same few mistakes trip up almost everyone and each one is easy to fix once you spot it. The first is filtering to late Power Query filters as it reads, so pushing your filters to the top of the query means later steps handle far fewer rows. Do that, and everything stays fast as your data grows. Leave a filter at the bottom and every step above it still chews through rows you were always going to throw away. Then put the expensive work last.

12:08 A sort has to read every row before it can show you a single result, while a filter streams through and returns rows as it goes. Keep the slow steps at the end and the fast ones at the start. Order matters while you're building. Use. Keep first rows to work against a small slice of the data, then delete that step. Once the query is finished, it keeps the editor responsive. Instead of making you wait on every click. The biggest one is query folding, and it's worth understanding even as a beginner. When your source is a database, Power Query can push supported transformations back to that database and let it do the heavy lifting, which is often far faster than pulling everything into Power Query first. Flat files like CSV and Excel can't fold, so the same query runs differently depending on where the data lives.

12:48 Get this one right and your queries stay quick even when the data grows into the millions of rows, a few habits cover the rest. Name your steps so future. You can tell what they do. Split a long query into smaller reference pieces and use parameters. So a value like a server name lives in one place instead of being retyped across a dozen steps. Two more habits save beginners a lot of grief. Turn off the load on any query you're only using as a stepping stone, so it doesn't land as a straight table nobody asked for and stop hardcoding file paths. You'll have to change every time the source moves. Recap. And one question. So the short version looks like this. Power query is the data cleaning engine built into Excel and Power BI, and every click becomes a recorded step. You can rewind, rename, or delete. You connect, transform, combine and load, then refresh whenever the data moves.

13:32 Merge, widens a table, append lengthens it, and getting your data types right early saves you the errors later. The shift worth noticing is that you're not cleaning data anymore. You're building a small process that cleans itself, and that's a very different kind of work. So I'll leave you with one question. Where in your own work does the same manual cleanup keep showing up and what would it look like if it ran itself?