Power QueryExercises

Is Power Query worth learning? Who it helps and who can skip it

Career · By Tahir Nas · Examples run in our simulator · Updated

9 min read

Power Query is worth learning if you regularly prepare data in Excel or Power BI: importing exports, cleaning them, combining files or reshaping reports that arrive in the same messy layout every week or month. You can skip it if your data already arrives clean, if you rarely repeat the same preparation twice, or if your work happens entirely in a database or a programming language with its own tools.

What you're actually learning

Power Query is the data preparation engine inside Excel, Power BI and several other Microsoft products. You connect to a source, apply steps such as removing columns, fixing types or merging tables, and Power Query saves those steps as a query you can refresh (What is Power Query?, Microsoft Learn). Behind the editor, every step is written in a formula language called M, which you can read and edit when the buttons aren't enough.

The skill isn't really "using a tool". It's learning to describe a cleaning process once, as steps, so that the computer repeats it. That idea carries across hosts: Microsoft's product table lists Power Query in Excel for Windows and Mac, Power BI, Power Apps, Power Automate, Azure Data Factory, Data Factory in Microsoft Fabric and others (Where can you use Power Query?, Microsoft Learn). Learn it in Excel and most of it applies in Power BI, as the lesson on Power Query in Excel and Power BI explains.

Who it helps most

People who clean the same export every period

This is where Power Query earns its keep. If you download a report each week or month and then spend time deleting header rows, fixing dates, removing blanks and pasting it under last month's data, all of that can become a query. Next period you refresh instead of repeating the work. The post on replacing a monthly copy-paste process shows the full routine.

The example below shows why. A monthly sales export gains a new month column every time. The query unpivots "every column except Product", so it never names a month, and the new column is picked up automatically:

let
    // This month's export has a new "Apr" column; the steps don't name any month
    Source = #table({"Product", "Jan", "Feb", "Mar", "Apr"}, {{"Kite", 12, 15, 11, 14}, {"Reel", 4, 6, 5, 7}}),
    Long = Table.UnpivotOtherColumns(Source, {"Product"}, "Month", "Units"),
    Totals = Table.Group(Long, {"Product"}, {{"Units", each List.Sum([Units]), type number}, {"Months", each Table.RowCount(_), Int64.Type}})
in
    Totals

Result

ProductUnitsMonths
Kite524
Reel224

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The result is Kite with 52 units over 4 months and Reel with 22 units over 4 months. When May arrives as a fifth column, the same query counts it without any change. Building queries that survive changes like this is the skill most worth learning, and the guide to unpivoting columns so new months are picked up goes into it.

Analysts moving into Power BI

If you are heading towards a Power BI role, Power Query isn't optional. The post on Power Query skills on a CV covers how to show it. Microsoft's study guide for the PL-300 Power BI Data Analyst exam says candidates should be proficient at using Power Query and DAX, and gives the "Prepare the data" area 25–30% of the exam (Study guide for Exam PL-300, Microsoft Learn). That area lists connecting to sources, profiling and cleaning data, choosing data types, pivoting and unpivoting, and merging and appending queries.

Finance and operations teams with spreadsheet processes

Month-end reconciliations, budget-versus-actual reports and stock lists are often built by hand from several exports. Power Query lets one person build the process and others refresh it, with each step visible in the Applied Steps pane. The guide to Power Query for finance month-end covers the common recipes.

People who maintain fragile macros

If a workbook relies on a VBA macro to import and tidy data, Power Query can usually replace that part with refreshable steps and no macro-enabled file. The post on Power Query vs VBA macros covers when to switch and when a macro is still the right tool.

Who can skip it (or learn only the basics)

Power Query isn't for everyone, and being honest about that saves time.

  • Your data already arrives clean. If a colleague or a data team hands you tidy tables, there's little to prepare. A pivot table or a Power BI report on top may be all you need.
  • The work is genuinely one-off. If you clean a file once and never see that layout again, formulas or manual edits can be quicker than building a query. Power Query's advantage is repetition.
  • You work mainly in SQL. If the data lives in a database you can query, cleaning it in SQL before it reaches Excel or Power BI is often the better place. When the source is a database, Power Query itself tries to push steps back to it through query folding (Understanding query evaluation and query folding, Microsoft Learn), so the two skills overlap.
  • You work mainly in Python. pandas covers the same cleaning jobs, and if your work happens in notebooks there's no need to switch. The post on Power Query vs Python pandas compares them task by task.
  • You need automation rather than data preparation. Sending emails, moving files and approvals are jobs for Power Automate, not Power Query, as the post on Power Query vs Power Automate sets out.

Even in these cases, an hour with the basics is useful, because you may inherit someone else's query and need to understand what it does.

How long does it take to learn?

It depends on what you count as learned, so think of it in stages rather than a single figure. The post on how long it takes to learn Power Query gives a planning timeline for each stage; in short:

StageWhat you can doWhat it takes
First useful queryImport a file, remove columns, filter rows, fix types, load to a sheetA few practice sessions with your own files
Everyday cleaningMerge and append, unpivot, group by, fill down, combine a folder of filesRegular practice on varied, messy data
Robust queriesQueries that survive new columns, missing files and bad values; parametersLearning to think about next month's file, not just today's
M fluencyReading and editing the code, writing custom functionsComfort with let, each and lists and records

Most people get value at the everyday cleaning stage. You don't need M to start, and many useful queries never need a hand-written line. When you do want to look under the hood, the lesson on reading the M behind the Applied Steps is the gentlest way in.

What to learn first

If you've decided it's worth it, learn in the order you'll use it:

  1. Import and inspect. Bring in a CSV or Excel table and look at what Power Query did with types and headers.
  2. Clean columns. Remove, rename, change types (with a locale if dates or numbers are written for another country), trim text.
  3. Filter and sort rows. Including understanding how nulls and blanks behave in filters.
  4. Combine tables. Append for stacking, merge for lookups, and how to check row counts afterwards.
  5. Reshape. Unpivot, pivot, group by and fill down.
  6. Make it refresh-proof. Steps that don't break when a new column or a bad value arrives.

The Power Query for beginners guide walks through the first steps. If you'd rather practise than read, the Foundations learning path on Power Query Exercises runs in the browser, needs no install, and checks each answer against hidden test cases.

Frequently asked questions

Is Power Query still relevant with AI tools and Copilot?

The need it meets, turning messy exports into reliable tables on every refresh, hasn't gone away. Whatever writes the first draft of a query, someone still has to check that it handles nulls, new columns and changed layouts, and that requires understanding the steps. Learning Power Query is what lets you review a query rather than just accept one.

Do I need to learn M?

Not at first. The editor writes M for you, and you can build useful queries without typing any. Learn to read M once you are comfortable with the editor, because it makes fixing broken steps much easier, and learn to write it when the buttons can't express what you need.

Is Power Query available on a Mac?

Microsoft's product table lists Power Query Desktop for Excel for Mac as well as Excel for Windows (What is Power Query?, Microsoft Learn). Connectors and features can differ by host, so check the connector you need before relying on it.

Sources

About the author

Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.

Keep reading

Practise Power Query in the browser with graded exercises checked against fresh data.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.