How to Update Data in Power BI

Fresh, accurate data is the lifeblood of any useful Power BI report. A dashboard showing last month's numbers isn't very helpful for making decisions today. This guide will walk you through the different ways to update your data in Power BI, from a simple manual click to a fully automated schedule that keeps your reports current without you lifting a finger.

Understanding How Power BI Handles Data

Before jumping into the "how," it's crucial to understand that the way you connect to your data sources fundamentally changes how refreshes work. There are three primary connection modes in Power BI, and your choice will dictate your refresh options.

1. Import Mode

This is the most common method. When you use Import Mode, Power BI takes a copy, or snapshot, of your data from sources like Excel files, SQL databases, or web pages and loads it into your Power BI file (.pbix file). Because you're working with a copy, the data doesn't update automatically. You have to tell Power BI to go back to the source and get a new snapshot. All the charts and tables you build are fast because the data is stored directly within Power BI.

• Pros: High performance, full Power Query and DAX functionality.
• Cons: Data can become stale, and file size can get large. Requires a scheduled refresh to stay current.

2. DirectQuery Mode

With DirectQuery, no data is actually stored in your Power BI file. Instead, every time you interact with a visual - like clicking on a bar in a chart or applying a filter - Power BI sends a query directly to the original data source to fetch the latest information. This is ideal for very large datasets or when you need real-time data.

• Pros: Data is always current, handles massive datasets that won't fit in memory.
• Cons: Slower performance as it depends on the speed of the source, a limited subset of Power Query functions are available.

3. Live Connection

Live Connection is similar to DirectQuery but is used specifically for connecting to tabular models like SQL Server Analysis Services (SSAS), Azure Analysis Services (AAS), and existing Power BI datasets. Like DirectQuery, your report sends live queries to the source, and a local data model is not required because it's managed by this database.

• Pros: Connects to enterprise-grade data models, data is always current.
• Cons: You cannot modify the data model within Power BI Desktop.

For the rest of this guide, we will focus primarily on the Import Mode, as it’s the one that requires you to actively set up and manage refresh schedules.

Method 1: The Simple Manual Refresh in Power BI Desktop

When you're actively building and designing a report on your computer, a quick manual refresh is the easiest way to pull in the latest data.

This method updates the data copy stored within your .pbix file. Think of it as hitting the "update" button on an app. It's perfect for when you've just added new rows to your source Excel file or know new data has come into your database.

Here’s how to do it:

1. Open your report in Power BI Desktop.
2. In the Home tab of the ribbon at the top, you'll see a prominent Refresh button.
3. Click it.

That's it. Power BI will now reach out to every data source connected to your file, fetch the current data, and update all the visuals in your report. You can see the status of the refresh in a small pop-up window.

Common Issue: If a data source has moved (like a file being renamed or a server address changing) or if credentials have expired, the refresh will fail. Power BI Desktop will prompt you to fix the connections in the "Data source settings" menu.

Method 2: Automating Updates with Scheduled Refresh in Power BI Service

Manually opening a file and clicking "Refresh" every morning isn't efficient or scalable. The real power comes from publishing your report to the Power BI Service (the cloud-based platform) and setting up a schedule to automatically update the data. This way, anyone viewing the shared report online will always see the latest information.

Prerequisite: The On-Premises Data Gateway

The first question the Power BI Service needs to answer is: "How do I access your data source?"

• For Cloud Sources (e.g., SharePoint, Salesforce, Azure SQL): Power BI can connect to these directly over the internet. You just need to provide your login credentials. No gateway is needed!
• For On-Premises Sources (e.g., an Excel file on your PC, a SQL Server inside your company's network): These sources are not publicly accessible on the internet. This is where the On-Premises Data Gateway acts as a secure bridge and comes in. Think of it as a secure doorman that lets the Power BI service securely access data that is firewalled or not publicly readable.

The gateway is a small piece of software you install on a computer that is always on and connected to your local network. It safely processes refresh requests from the Power BI Service, fetches the data from your local source, and sends it back to Power BI in an encrypted fashion. You will need this for the refresh to find your data.

Step-by-Step Guide to Configuring a Scheduled Refresh

Let's set up an automatic daily refresh for a report.

Step 1: Publish Your Report

First, you need to get your report from Power BI Desktop into the Power BI Service. In Power BI Desktop, click the Publish button on the Home ribbon and select a destination workspace. After it's successfully published, it will be available to you on app.powerbi.com.

Step 2: Go to Dataset Settings

In the Power BI Service (app.powerbi.com), find the workspace where you published your report. You will see both a Report and a Dataset with the same name. Keeping your reports up-to-date means managing the dataset, which is the link back to the data models, data connections, tables, and credentials required in the dashboard report itself.

Hover over your dataset, click the three-dot menu (...), and select Settings.

Step 3: Edit Data Source Credentials

Under the "Data source credentials" section, you’ll see each source used in your report. Power BI Service needs its own permissions to access them. Click Edit credentials and sign in for each one. Often, you'll use an authentication method like "OAuth2" for cloud services, which opens a familiar login window for sources like Salesforce or SharePoint.

Step 4: Configure the Gateway Connection (If Needed)

If you have any on-premises data sources, a "Gateway connection" section will appear. Make sure your gateway is running and showing as "Online." You’ll then need to map each on-premises data source to a corresponding source you've already configured within the gateway management page. If it all lights green, you are good to go!

Step 5: Set the Refresh Schedule

In the settings, expand the Scheduled refresh section to configure one. Toggle the switch to On.

• Refresh frequency: Choose between daily or weekly refreshes for all licenses.
• Time zone: Select YOUR timezone to schedule specific hours during your business hours.
• Add another time: If you are running on a 'Pro' version, you are allowed up to eight automatic refresh timings every day! Running it premium? That number grows to over 48... This helps spread out resources needed at particular intervals.

Method 3: Additional Ways for Updating

Besides the Desktop client and service configuration, there are more options. You can get even fresher information when working within reports and datasets directly.

Trigger an Instant Refresh

Waiting for the next schedule? Too much work opening the desktop file? No worries. Simply open the report. On its upper-right-hand dashboard, you can force another refresh at any given moment, and Power BI will prioritize it.

The Other Route...

When the data demands near-minute accuracy, the option called 'Automatic Page Refresh' is a premium feature available. This feature works for DirectQuery or Live Connection.

Set an automatic page by:

• Open your report design and click anywhere away from your visualization canvas area.
• Go to the Visualizations section, select Format, and then you should see the tab 'Page Refresh'. Toggle it ON to show any interval choices.
• The default setting should refresh about every 30 minutes, but depending on the license plan, premium licenses unlock the minimum interval to even 1 second!

Best Practices for Reliable Refreshes

Keeping automation afloat requires little hygiene. Follow these best practices:

• Optimize your model: Slimmer is swifter. Remove unused columns and calculations. Summarize complex calculations.
• Check password expiration: Ensure credentials are up-to-date to avoid refresh failures.
• Favor cloud sources when available: This avoids the need for installing gateways.
• Stagger refresh times: Many set them for 9:00 AM Monday, which strains systems. Spread timings to ease the load.
• Monitor refresh history: Use logging to track errors and diagnose issues.

Final Thoughts

Effectively updating data in Power BI involves a progression of steps and some architectural considerations. Start easy, from direct hands-on desktop clicks up to server-side automations. Knowing if your sources live at local sites or on cloud platforms determines the necessity for setup. Consistently delivering fresh data builds your reporting's foundations on trust, a crucial analytical trait for data-driven decisions.

Building reliable data pipelines by configuring refreshes from on-premises database sources or servers can pose a challenge. It's a task needing skills from IT professionals. At Graphed, we aim to simplify this process by removing the need for complex gateway setups. Our platform connects seamlessly with common SaaS solutions, keeping your campaigns up-to-date without requiring new schedules in Power BI.