Frequently Asked Queries

Power Query is a tool for getting, cleaning, combining and reloading data. Think of it as a way to record your manual Excel steps so you can run them again later.

Microsoft describes Power Query as a data transformation and data preparation engine with a graphical interface for fetching and shaping data. So you don't need to write code to get started.

Power Query is especially useful when you notice you're doing the same work over and over. Think of monthly reports, system exports, combining files from a folder, cleaning columns, fixing dates, removing duplicates or joining tables.

If you frequently catch yourself thinking "I did exactly this last month too", that's usually a strong Power Query signal.

In Excel you'll find Power Query under the Data tab, usually under Get & Transform Data. Microsoft also refers to Power Query in Excel as Get & Transform.

You can use it to import or connect to external data, shape it, and then load it into Excel so the query can be refreshed later.

No. That's exactly one of the reasons Power Query is so strong for people who work a lot with Excel. You start with clicks: removing columns, setting filters, changing data types, combining tables.

Behind the scenes Power Query writes M code, but you don't have to write it yourself in the beginning. Later it becomes useful to read it and tweak small parts.

Start with something you already know. For example one monthly export you currently clean by hand. Drop that export in a fixed folder, load it via Power Query and apply your usual cleaning steps.

Then replace the file with next month's version and click refresh. That's often the moment the penny drops.

Things like removing columns, renaming columns, removing empty rows, changing data types, splitting text, replacing values, applying filters and using the first row as headers.

Microsoft refers to these as standard transformations in the Power Query editor.

Yes — and this is one of the most practical scenarios. Suppose every month you receive an Excel or CSV file with the same columns. You can point Power Query at a folder and combine all files in it into one table.

Microsoft specifically describes this as the scenario for files with the same structure being combined from a single folder.

Merge is similar to a lookup. You connect two tables side by side using a common key, for example a customer number, product code or employee ID.

Append means you stack tables on top of each other — for example January, February and March stacked into one yearly table. In Power Query these actions are called Merge and Append.

Yes. Power Query also supports group by, pivot and unpivot. You'd use group by to create totals per customer, region or month. Pivot turns row values into columns.

Unpivot is often even more important: it turns wide Excel-style tables back into something analysable. Microsoft lists merge, append, group by, pivot and unpivot as more advanced transformation options in Power Query.

Every transformation you apply lands in Power Query as an applied step. That's how you can trace what's happening to your data: source first, then remove columns, then change types, then filter, and so on.

Microsoft describes the Applied Steps list as the place where transformations become visible and where you can verify what each step does to the data.

No. Power Query lives in several Microsoft products. Microsoft mentions Excel, Power BI, Analysis Services, Dataverse and other products as places where Power Query is used for data connectivity and data preparation.

In Power BI Desktop you use the Power Query Editor to connect to one or more data sources, clean the data and then load it into your model.

That's typically the step before you start working with relationships, DAX and visuals.

Dataflows are the cloud version of this idea. Instead of using Power Query inside one Excel or Power BI file, you can prepare data in an online environment and reuse the result.

Microsoft describes dataflows as self-service, cloud-based data preparation that lets you ingest, transform and store data into Power BI workspaces, Dataverse or Azure Data Lake Storage among others.

In Microsoft Fabric there's Dataflow Gen2. It feels familiar if you know Power Query, because it also lets you use Power Query Online to ingest, transform and load data.

Microsoft describes Dataflow Gen2 as a way to visually build multi-step data ingestion and transformation using Power Query Online.

The core idea is the same: get data, record steps, transform data and load the result. But where the data ends up differs per tool. In Excel you usually load to a worksheet or data model. In Power BI you load to your semantic model. In Dataflows or Fabric you can store the result more centrally and reuse it.

Microsoft also notes that the destination depends on the product or service in which Power Query is used.

Learn to read your steps. Not the entire M language right away, but enough to understand what each step does. Give your steps clear names, remove unnecessary ones and check regularly that your query still makes sense.

Power Query becomes a lot easier when you see it as a small process rather than a sequence of disconnected buttons.

Not right away. But at some point it does help. Power Query uses the M formula language to filter, combine and merge data. Microsoft describes M as the language Power Query uses to filter, combine and merge data from one or more sources.

Start small: see what happens when you add a column, set a filter or replace a value. The easiest way to learn M is often by reading back what your own clicks produced.

Query folding means Power Query can translate certain steps back to the source — for example into a SQL query. That way Power Query doesn't always have to fetch all the data first before filtering or grouping happens.

Microsoft describes query folding as the process where M queries are processed and translated into requests against the data source. For beginners it's not yet a starting point, but as datasets grow, it becomes important.

A few practical habits help a lot: pick the right connector, filter as early as possible, do expensive operations later, and work with a smaller subset while you're building.

These are also best practices Microsoft itself lists for Power Query. In plain language: don't pull in everything if you only need part of it.

Not because it sounds "more professional", but because your needs change. Excel is fine for personal analysis and small recurring reports. Power BI starts to make more sense once you build dashboards, data models and shared reports.

Dataflows or Fabric become interesting once the same cleaned data is used by multiple reports or people. The technology follows the question: who uses it, how often, how much data is it, and how repeatable does it need to be?