From manual export to scheduled refresh in Power BI: Power Query and dataflows

If your reporting routine includes exporting a spreadsheet, pasting it into a file, and re-cleaning it every month, this guide is for you. We will move that work into Power Query so the cleanup runs itself, parameterize the source so it is easy to point at a new file or server, promote the logic into a dataflow so several reports can share it, and then set up scheduled refresh so the data updates on its own. This is aimed at the SMB analyst who builds reports by hand and is tired of the monthly ritual.

Before you start

• Power BI Desktop installed, and a Power BI (Fabric) account with permission to publish to a workspace.
• A data source you currently export by hand: a file, a folder of files, a database, or a web feed.
• For on-premises sources (a SQL Server on your own network), an on-premises data gateway already installed, or a plan to install one.

1. Shape the data in Power Query

1. In Power BI Desktop, on the Home ribbon select Get data, choose your source, then select Transform data to open the Power Query Editor.
2. Remove what you do not need: select columns and use Remove columns, and use Remove rows for headers or blanks that came along.
3. Set every column's type explicitly. Click the type icon to the left of each column header and choose the right type (text, whole number, decimal, date). Wrong types are the most common cause of refresh failures later.

2. Merge and append when you have more than one source

• Append stacks tables with the same columns on top of each other (for example, twelve monthly files into one). Use Home > Append queries.
• Merge joins two tables side by side on a matching key (for example, adding customer details to an orders table). Use Home > Merge queries, pick the matching columns, and choose the join kind (a left outer join keeps all rows from the first table).

3. Parameterize the source

Hard-coding a file path or server name means editing the query every time it changes. A parameter fixes that.

1. In the Power Query Editor, on the Home ribbon select Manage parameters > New parameter.
2. Name it (for example, SourceFolder or ServerName), set the type to Text, and give it a current value.
3. Open the first step of your query in the Advanced editor or the formula bar and replace the hard-coded path with your parameter name.

4. Promote the logic to a dataflow

A dataflow runs your Power Query logic in the Power BI service, on a schedule, so multiple reports consume the same cleaned data instead of each repeating the work.

1. In the Power BI (Fabric) service, open a workspace and select New item (or New), then choose Dataflow (Dataflow Gen2 in a Fabric-enabled workspace).
2. In Power Query Online, select Get data, connect to your source, and rebuild (or paste) the same transformations you built in Desktop.
3. Name the dataflow and select Save (or Save & run in Gen2) to publish it.

5. Point your report at the dataflow

1. Back in Power BI Desktop, on Home > Get data choose Power BI dataflows (or Dataflows).
2. Select your dataflow's tables and load them. Your report now consumes pre-cleaned data, and the transformation logic lives in one shared place.

6. Publish and set up scheduled refresh

1. Publish your report from Desktop with Home > Publish and pick the workspace.
2. In the service, find the semantic model (dataset) behind your report, open its Settings, and expand Scheduled refresh.
3. Toggle scheduled refresh on, choose a frequency (daily or weekly), set the time zone, and add one or more refresh times.
4. If the source is on-premises, set the Gateway connection to your on-premises data gateway and map the data source credentials.
5. Do the same on the dataflow's settings so the dataflow refreshes before the report that depends on it.

7. Add incremental refresh for big tables

When a fact table is large, refreshing every row each time is slow and can time out. Incremental refresh reloads only recent data.

1. In Power BI Desktop's Power Query Editor, create two Date/Time parameters named exactly RangeStart and RangeEnd (the names are case-sensitive).
2. Filter your fact table's date column so it keeps rows where the date is after or equal to RangeStart and before RangeEnd.
3. Close Power Query. In the Fields pane, right-click the fact table and choose Incremental refresh.
4. Turn it on, set Archive data starting (how much history to keep) and Incrementally refresh data starting (the recent window to reload each time), then publish.

8. Verify the pipeline runs end to end

1. In the service, open the dataflow and select Refresh now; confirm it completes without error.
2. Refresh the semantic model and confirm it succeeds and pulls the dataflow's latest data.
3. Check the Refresh history on both for green, on-time runs.
4. Change one record at the source, wait for the next scheduled run (or trigger a manual refresh), and confirm the change shows up in the report.

What to do next

With this pipeline in place, the monthly export ritual is gone: the data shapes itself, refreshes on a schedule, and several reports share one clean source. The next refinements are tuning refresh times around your business hours and adding incremental refresh to any other large tables. If your source sits behind a firewall or you are wiring up a gateway for the first time, that is a common sticking point, and one we set up for SMBs regularly. Let's get it running for you.