Skip to content

5 easy ways to change data source in Power BI

Change data source in Power BI

Many like to build Power BI solutions in a development environment with lower data quantities. This means you can develop faster. But anybody who follows this method will soon come to an obvious question when they want to use live data: “how to change data source in Power BI?”.

In this post, we look at the solutions to that very question.

When we build a Power BI report, selecting a data source occurs at the start of the process. We then build pages and visuals; and finally publish the report to the Power BI service. Therefore, changing data sources involves going right back to the start.

All examples in this post use a locally saved CSV file as the data source. If you are working with other data, the details may differ, but principles the still apply.

OK, let’s get started. We have 5 easy to follow methods to take you through.

Table of contents

Change query M code

In most queries, connecting to the source is normally the first item in the applied steps list. It isn’t always first (as we will see), but since we tend to work from start to finish, it’s a sensible place to look first.

Change data source using Formula Bar

To change the source of any individual query:

  • In Power BI, click Home > Transform Data
  • Select the first step in the applied steps (probably called Source, but no guarantees) [1]
  • Change the source referenced in the formula bar [2]

Change data source using Advanced Editor

The formula bar displays only one step. The Advanced Editor shows all the steps in the query. Therefore, the same change can be made in the Advanced Editor.

In Power Query:

  • Click View > Advanced Editor [1],[2]
  • Change the source [3]
  • Click Done to close the Advanced Editor [4]
Change data source in the Advanced Editor of Power BI

Change data source using step settings

Another method, where we change the source, is within the step parameters. Within the applied steps pane, click on the gear icon next to the Source step[1]. This opens the relevant dialog box [2].

Change source from the gear icon in the applied steps

Depending on the source type, the dialog box will look different. Whatever your source is, update the location of the data source, then click OK.

This method only changes the source for a single query. It does not change any other queries using this source.

Data source settings

The second method to change data source in Power BI is using the data source settings.

The data source settings are helpful where a source is used in multiple queries. This method repoints all queries at the same time.

  • In Power BI, or from the Power Query editor, click File > Options and settings > Data source settings
  • In the data source settings dialog box, select the source [1], and click Change Source… [2]
  • This opens context dependent dialog box [3], so it may differ for your scenario. Enter the new source details and click OK.
  • Click Close on the data source settings dialog box
Change main data source settings

As noted above, instead of changing a single query, this will change all queries using that source.

Using parameters

The paths for data sources can also be held within parameters. You will be able to identify this situation based on the Source step within the formula bar.

As shown by the screenshot below, this step refers to a parameter:

Parameter used to declare the data source

To change this parameter:

  • In the Power Query editor, click Home > Manage parameters [1],[2]
  • The Manage Parameters dialog box opens [3]
  • Select the parameter from the list
  • Change the Current Value field [4]
  • Click OK [5]

This method repoints all queries that are dependent on the parameter.

Using static values

Parameters are a special type of object which have a variety of uses in Power BI. A simpler, but related approach uses a static query or static step to hold the data source path.

A static query looks like this:

Creating a static value query

This static query is used within the source, as shown below.

Static value used in the Source Step

That static value can also be defined as a step from within the same query and used in a similar way.

Look at the screenshot below. There is a new step called FilePath. This step holds a static value which references the data source path.

FilePath as a separate step in the same query

Note: This is a circumstance where the the Source is not the first step in the query.

The static value from the step above has been used within the File.Contents function as the data source path.

Source references another step with a static value

Both of the methods shown in this section use fixed values at the source. To change the source, just replace the value with the new path.

Workbook parameters

The final method uses an Excel workbook to hold the source path. This is a very flexible way to change data source in Power BI. End users can change it themselves without any understanding of Power BI.

The Formula.Firewall error is a common error when using this method. This error occurs when an external source defines the source of another query. The solution to this error is combining queries into a single query. As a result, source workbook is referenced from within the same query.

We will cover how to create this method in a future post. But for this example, the Formula.Firewall error has already been resolved.

Here is the code from the advanced editor.

External source detailing the source for another query

Open up the workbook in named in the M code. Change the value within the workbook named range. Then save and close the Excel workbook.

Named range containing the file path as the data source

When we next refresh Power BI, it gets the new value from the workbook.

While I have referred to these as Workbook Parameters, similar methods can be used with other sources, such as CSV or Text.

Warnings

Workbook parameters may cause issues when working with SQL queries as they can break Query Folding and cause slow refresh times.

When replacing data sources, it is important for the new source to have an identical format. However, there are some circumstances where we know the structure will change. When this happens, we need to build that complexity into our query design. The most common issue is changing column names. One helpful method to deal with this is to rename columns by position.

Wrap-up

In this post, we have seen it is possible to change data sources in Power BI reports using various methods. The options available to you are dependent on the method chosen by the query designer. Even if you prefer to use data source settings, the query designer may have used a parameter; therefore, it is useful to know all the methods.

Other resources

Looking for more resources on how to change data sources using Power Query, then take a look at this Excel resource: Power Query – Change the source data location

If you are experiencing issues, the Power BI Community is often a great place to get support.