What Power Query does in the BI stack
ETL stands for extract, transform, load. Extract means pull the raw file, transform means fix and reshape it, and load means deliver a clean table to the model. Power Query handles all three inside Power BI, so your report never touches messy source rows. The transformations are saved as a step list you can audit and reorder.
Doing this work in Power Query, not in DAX, keeps your measures simple. A measure should answer a question, not scrub commas out of a text amount. Clean once at load time and every visual benefits.
Extract the raw data
On the Home ribbon click Get Data and pick Text/CSV for a file export. For a database pick SQL Server and enter the server and the Orders table name.
Power Query opens a preview of the Sales CSV with 42,000 rows. Check the first twenty rows for stray headers or blank lines before you commit.
Click Transform Data to enter the Power Query editor. You now see the Applied Steps pane on the right, which records everything you do from here.
If the source is a folder of monthly files, point at the folder instead of one file. New files dropped in the folder are picked up on the next refresh automatically.
Transform the columns
Right-click the OrderID column, choose Remove Empty Rows. The 42,000 rows drop to 41,872 once trailing blanks from the export are gone.
Select the Amount column, choose Decimal Number from the type dropdown. If it was text with dollar signs, use Replace Values to strip the $ first, then change the type.
Select OrderDate, click Split Column by delimiter or extract Year and Month using the Date tools. This gives clean Calendar keys for later time intelligence.
I recommend setting every column type explicitly in Power Query, not letting Power BI guess at load. A column typed as text instead of date is the number one reason time intelligence returns blank.
Reshape for a star schema
Right-click the Sales query and choose Reference to create a Products query, then keep only ProductKey, ProductName, and Category. This builds your dimension without re-extracting the file.
In the Products query, click Remove Duplicates on ProductKey so each product appears once. The dimension drops from 41,872 rows to 300.
Right-click any helper query you do not need as a table and untick Enable Load. This keeps the model lean and hides intermediate steps from report users.
Mistakes that slow or break refresh
A common mistake is doing the cleaning in DAX with IF and REPLACE instead of in Power Query. Those formulas rerun on every interaction and make the report sluggish. Transform at the source.
This will break if you change a column type before you remove errors, because the type step fails on dirty values. Always clean, then type, then reshape, in that order.
Replacing a merge with a calculated column that looks up another table row by row is slow on 42,000 rows. Use a Merge Queries join instead, which Power Query optimizes.
Filter out your test orders before load, or they inflate totals. Keep a filter step on Status <> 'TEST' near the top of the Applied Steps.
Worked example: a 42,000-row cleanup
Start with a Sales CSV carrying OrderID, OrderDate as text, Amount with dollar signs, and a trailing blank section. In Power Query you remove empty rows (41,872 left), replace $ in Amount and set Decimal, split OrderDate into Year and Month, and remove duplicates to build Products. After load, Total Sales = SUM(Sales[Amount]) returns 1,245,300 and slices by the new Month column with no extra DAX.
If you know SQL, here is the map
Each Power Query step maps to a SQL idea: Remove Duplicates is DISTINCT, Merge Queries is a JOIN, and Group By is the SQL GROUP BY. The difference is that Power Query writes the steps visually and repeats them on refresh, while SQL you rerun by hand. Our SQL for data analysis guide covers the GROUP BY pattern if you want the database-side view.
When the source is already a clean database, let the SQL do the heavy grouping and use Power Query only for the final reshape. That split keeps refresh fast.
Merge and append to shape the model
Two transforms do most of the heavy lifting in Power Query. Merge Queries joins two tables on a key, like a SQL JOIN, so you can pull ProductName from Products into Sales without copying the whole dimension. Append Queries stacks tables with the same columns, such as twelve monthly files into one annual table for the fact.
In the Sales query, choose Merge Queries, pick Products on ProductKey, and expand ProductName and Category. The fact now carries the labels it needs for a star schema without a separate lookup at report time.
If the source is twelve files, use Append Queries to stack them into one Sales table of 42,000 rows. Each refresh re-reads the folder, so new months appear automatically.
Parameters and refresh settings
Parameters let one query serve many purposes without editing steps. A folder-path parameter means a colleague points the report at their own data drop and the same Applied Steps run unchanged. This is how you hand a repeatable ETL process to someone who never opens the query editor.
Refresh behavior matters as much as the steps. Set the data source credentials once, choose a scheduled refresh in the service, and confirm the gateway can reach the file. A perfect query that never refreshes delivers yesterday's numbers, which is worse than no report.
On the Home ribbon choose Manage Parameters, add SourceFolder as text, and replace the hard-coded path in your folder source with that parameter so the query reads from anywhere.
In the Power BI service, open the dataset settings, set a daily refresh time, and enter the gateway credentials. The 42,000-row load then repeats every morning with no manual step.
I recommend disabling load on any staging query you only use as a merge source, because leaving it enabled doubles the imported rows and slows the whole refresh for no reporting benefit.
Troubleshooting a failed refresh
When refresh fails, the error almost always points at one of three causes: a changed column name in the source, expired credentials, or a step that assumed a row order that no longer holds. Read the exact error, open the query, and step backward through Applied Steps until the preview breaks; that step is your culprit.
A common source-side break is a renamed export column. If finance renames Amount to SalesAmount, your Decimal Number step fails. Rather than chase the rename every month, promote a stable header or use a renaming step that maps the new name back to Amount before any type change.
Click each step in Applied Steps from the top. The first step that shows an error icon is where the transformation met data it did not expect.
After removing errors and setting types, add a Changed Type step explicitly so a future text value in Amount throws a clear error instead of silently breaking totals.
Clean messy text with Column From Examples
When a column mixes formats, such as 'West-01' and 'WEST 1' for the same region, Column From Examples lets you type a few correct outputs and Power BI infers the rule for the rest. It is faster than writing a long Replace Values chain and far easier to audit, because you can see the examples that drove the logic.
Select the Region column, click Column From Examples on the Add Column ribbon, and type 'West' next to the first 'West-01' row to teach the pattern.
Power Query shows a preview of every row transformed. Confirm West, East, North, and South are consistent, then click OK to add the cleaned column and remove the old one.
Use this for one-off cleanups, but for recurring feeds prefer explicit steps like Trim and Capitalize so the logic is predictable on every refresh. Column From Examples can drift if the source format changes, whereas a fixed Trim step always behaves the same.
Handling errors without losing rows
A single bad value can turn a whole column red and break the load. Instead of deleting error rows blindly, right-click the column, choose Replace Errors, and set a sensible default such as 0 or 'Unknown'. This keeps the row count stable at 42,000 and lets you investigate the bad values separately rather than dropping real data.
After setting Amount to Decimal, right-click the column, pick Replace Errors, enter 0. The two dirty rows become 0 instead of vanishing, and your total stays reconcilable.
Add a custom column that flags Amount = 0 after load, then filter to those rows in a separate query to see which source lines need a fix at the origin.
A common mistake is clicking Remove Errors to make the red go away, which silently drops rows and understates every total. Replace errors with a visible value so the gap is auditable.
What query folding buys you
When your source is a database, Power Query tries to push steps back to the server, a behavior called query folding. A filter or a grouping you apply in the editor becomes a SQL WHERE or GROUP BY that runs on the database, so only the rows you need travel to Power BI. You get the same result with a fraction of the data movement, and refreshes get dramatically faster.
Apply filters and merges early, before you add custom columns. Once a step cannot be translated to SQL, folding stops and the remaining work runs locally on every row.
Right-click a step and choose View Native Query. If it shows SQL, the step folded; if the option is greyed out, that step broke the fold and forced local processing.
For a 42,000-row CSV the benefit is modest, but against a million-row table it is the difference between a two-minute and a two-second refresh. Design the early steps to stay foldable, and reserve non-foldable custom logic for the very end of the query.
Scaling refresh beyond one file
As data grows, refreshing the whole table every time wastes resources. Incremental refresh loads only the recent partition and appends it to history, so a daily refresh touches a few thousand rows instead of the full 42,000. Pair it with a scheduled refresh in the service and the model stays current without a manual export.
In the dataset settings, define an incremental refresh policy such as 'store 5 years, refresh 1 day' so only the latest day reloads each night.
For an on-premises source, install and configure a gateway and enter its credentials, or the scheduled refresh fails silently and the report goes stale.
I recommend testing incremental refresh on a copy first, because a wrong date filter can drop historical partitions and leave gaps that are hard to spot until someone questions a year-over-year total.
Key takeaways
Power Query is where data becomes trustworthy, and the work you do there pays off on every page of the report. Clean once, set the types explicitly, reshape into a star schema, and let the recorded steps repeat on each refresh so you never scrub the same file by hand again.
The habits that matter are ordering the steps correctly, keeping early steps foldable for database sources, and replacing errors instead of deleting rows. Do those three and a 42,000-row export becomes a clean, fast-loading model that DAX can rely on without surprises.
I recommend saving a copy of the raw source file each time you refresh, so if a Power Query step ever breaks you can compare the new export against the last known-good one and find the change fast.
The one habit to keep
If you build only one habit, make it reviewing the Applied Steps pane before every load. A model that looks right on screen can still carry a hidden error step that corrupts a total weeks later, and the step list is the only place that mistake is visible. A thirty-second scan saves a much longer debugging session after publish.
Next steps
Build one query end to end on a sample CSV, then refresh after editing the file to watch the steps replay. Once the table is clean, move to modeling so the fact and dimensions link correctly. The data modeling guide shows how to relate the Products query you just built, and the DAX measures guide turns the clean Sales table into real reports.
If you are studying, the PL-300 guide lists Power Query as a scored section, and the Power BI for beginners guide places it in the full workflow.
