Do you need to change the data source used in your Power BI reports?

Creating queries in Power BI can be a challenge when it comes to modifying the data source. For instance, moving all queries from a test environment to production can be daunting. What’s the best way to handle this situation?

There may be a scenario where want to ensure that your end user can conveniently access and modify the source data for those queries. What is the best way to ensure he has edit access to the source?

Throughout this article, I will walk you through different methods that allow you to get this done. It is a basic yet important task to be able to change the data source in Power BI.

Therefore, go through this article until the end and enhance your skills. Ready to go?

Through the Data source settings

This is the easiest way to change the data source I must say. In a sense, even a person from non-technical background (from a Power BI point of view) with basic computer literacy should be able to work around it.

And the thing that makes it interesting is this is by far the only way of changing data sources through the Power BI report interface. All other ways discussed later in this article are to change the source through Power Query Editor.

Open the Power BI report and click on the Home tab.

Under the Queries section, click the Transform dropdown and select Data source settings.

💡 Tip: You can also access the Data source settings through the File menu. Go to File tab > Options and settings > click on Data source settings.

The Data source settings dialogue box will open up. Make sure the Data sources in the current file radio button is selected (it generally is selected by default).

You can see a list of data sources that are currently being used in your report. Click on the first source and then hit the Change source… button present in the bottom right corner. Click on it.

A new dialogue box will open. With the Basic radio button selected, you can click on the Browse button and change the path of the source data file. Once done, click on the OK button to change the data source.

After you are done changing each data source within the file, you can click on the Close button to close the Data source settings dialogue box.

Changing the M Code in Formula Bar

For those who are not acquainted with the M code present in the formula bar of Power Query, it may seem daunting. However, tweaking this code is a straightforward way to customize the data source in Power BI.

Changing the data source path in the M code can effortlessly allow you to change the data source in Power BI, and that too quickly. One of the easiest ways, I reckon! Let’s explore how.

To access the Power Query Editor inside the Power BI report, go to the Transform data dropdown in the Home tab and click the Transform data option.

When opening the Power Query Editor, the initial step in the APPLIED STEPS section of Query Settings is usually the Source step. The formula of this step includes the M code that specifies the location of the source workbook.

💡 Tip: If you can’t see the formula displayed above, it might be because the Formula Bar in your Power Query Editor is unchecked. To resolve this issue, go to the View tab and ensure that the Formula Bar is selected under the Layout options.

To update the source data location, replace the text within the double quotes in the provided formula with the path of your new data file, which in my case is, "C:\Users\saswi\OneDrive\Desktop\Power BI Datasets\Sales Data.xlsx." This ensures that your source data is accurately reflected at the specified location.

Once done, go to Home > Close & Apply dropdown > click the Close & Apply option to implement the changes and close the Power Query Editor.

Changing the M Code in Advanced Editor

In addition to the first method we discussed, you can modify the M code in the Advanced Editor and alter the file path to update the data source in your Power BI file.

To access the Advanced Editor, go to the View tab and choose the Advanced Editor option found in the Advanced section.

Inside the Advanced Editor that opens up, replace the current file path with the new one where you stored your source file and click on the Done button.

It will change the data source to the new location. In this example, the latest file path will be "C:\Users\saswi\OneDrive\Desktop\Power BI Datasets\Sales Data.xlsx".

That is another way to change the data source in Power BI through Power Query Editor.

Using the Edit Settings Option in Source Step

In the previous section, you were required to manually copy and paste the file path into the Formula Bar. However, this method may not be convenient for those who prefer to navigate through folders.

In this section, I will guide you on how to use the Edit Settings option in the Source step to browse and locate the file path more easily.

To proceed, go to the Source step and select the Edit Settings icon to reveal a dialogue box. The default mode is currently set to Basic, which should suffice for your needs at the moment.

To change the file path, simply click on the Browse… button located in the File path section. Then, navigate to the new path where the dataset is now stored.

That’s it. You changed the data source successfully through the Power Query Editor by browsing the file path.

Using an Excel Cell to Store and Change the Source Path

Until now, we were doing the update by changing the data source setting in the Power Query either through the Formula bar or through Advanced Editor.

However, all these are static ways of changing the data source. Meaning if the file location changes, you need to follow these steps again to make sure the source file location is correctly captured.

Instead, using the Excel cell values as parameters is a great way to deal with a situation where you have different locations that can be stored on different paths.

In this case, the data admin (or the person handling the source data) will update the file’s path in the Excel cell. That Excel cell will then be used as the parameter in Power Query to fetch the data from that file automatically.

Suppose you have an Excel Path Picker file. Here, the person who prepares the source data stores the file location with the file name. You want Power BI to go to this location and pick the file named “Sales Data.xlsx.”

Go to the Power BI Get Data dropdown and choose the Excel Workbook option. Please navigate to the path where the Path Picker.xlsx file is stored and open it.

Inside the Navigator, select the Path_Table, where the path is mentioned in a tabular form. You will do all the required work in Power Query. Hence click on the Transform Data tab to launch the Power Query Editor.

Inside the Power Query Editor, right-click on the cell where the file path is stored and select the Drill Down option.

This will convert the query into a single text value, which later can be used as a path to fetch the Sales Data.xlsx file, which now looks like the one above.

Please note the difference now, The Path_Table is not a query anymore but a single text value representing the path of the file Sales_Data.xlsx.

Now, go to the View tab and click on the Advanced Editor option to launch it. Here, you can see the entire M Code associated with all the steps you followed.

At the end of the M code, type the following piece of code before the in keyword and hit the Done button.

SalesData = Excel.Workbook(File.Contents(Path))

• SalesData – is an object that can hold the output of the step performed.
• Excel.Workbook – is an M language function that returns the contents of the Excel workbook.
• File.Contents – is again an M language function that returns the file’s contents in a binary format.

Now, what happens here in this small piece of code is the File.Contents() function takes the value from the Path object, which is nothing but a text path for the Sales_Data.xlsx file.

Since that Path is a binary value, Excel.Worbook function will not be able to recognize it as a path, so the function is used. Excel.Workbook then takes the Sales_Data.xlsx workbook from that path and stores the result in the SalesData object.

The amended M-Code snapshot is as shown in the screenshot above.

📝 Note: Please don’t forget to add SalesData after the in keyword in M-Code under Advanced Editor.

Once you click on the Done button, the Advanced Editor closes down, and you see a new step named SalesData inside the APPLIED STEPS section on the right-hand side of the Power Query Editor.

Also, on the left, you see a tabular structure where three different components of the Excel file you just fetched are shown with different properties.

The first object from that list is a Sales Data sheet from that Excel file. The second row represents the second object, an Excel table object named Sales_Table.

You need to click on the Table option from the second row of the Data column, and this will navigate to the Sales_Table from the Excel file and expand the table itself. Also, a new step will be added to the APPLIED STEPS section.

The Sales_Table looks like the one shown in the screenshot above.

Changing Data Source through Parameter in Power BI

One of the key features of Power BI is the ability to change data sources using parameters dynamically. This lets users easily switch between different data sources without manually modifying the connection settings.

In this section, I will guide you through the step-by-step process of changing data sources using parameters in Power BI.

The first step is to create a parameter that will serve as the input for selecting the desired data source. To do this, open the Power BI desktop application and navigate to the Home tab.

Click on Tranform data. There is a Manage Parameters dropdown inside the Home tab of the Power Query Editor. Click on it. Select the New Parameter option to create a new parameter.

Configure the parameter properties inside the Manage Parameters dialogue box that pops up.

1. Provide the Name of the parameter as the Data Source (You are free to use anything of your choice as a name).
2. Add a small Description of the parameter you are creating (totally optional but good to have).
3. Set the parameter Type as Text through the dropdown because we will use a path for the file, which will be in text /string format.
4. Suggested Value dropdown should be kept as it is (the default is Any Value).
5. Under the Current Value section, add the path of your source file (the path where the file is stored).
6. Click on the OK button to create this parameter.

Once this parameter for the source data is created, it will look like the one shown above.

Now, click on the Sales Table, and from the APPLIED STEPS section, click on the Settings icon of the Source step (the first step in the editor).

on the dialogue box that pops up,

1. Click on the Advanced radio button
2. Add one more section to the File path parts.
3. In the first part, change the dropdown to a Parameter, and the Data Source parameter will automatically appear.
4. In the second part, keep it as Text and add \Sales Data.xlsx as an added extension into it.
5. Check the File path preview, and keep the Open file as dropdown set to Excel Workbook because that’s how you wanted to open this file.
6. Finally, click on the OK button to change the source of the Sales_Table in Power Query Editor and eventually Power BI.

Explanation

• Here, the path created as a parameter is first used as an input.
• Since that path is dynamic, we need to provide a file name as a text so that Power BI can understand which file to look up to as a data source on that path.
• Finally, we checked how we wanted to open that file. There are many options, depending on the file type(CSV, TXT, etc.)

Whola!🤩 You have successfully updated the data source of your Power BI Sales_Table dynamically.

You can now click the Close and Apply dropdown to load this data into Power BI for further analysis.

What is the advantage of this method? Well, imagine you are working with multiple datasets, each of which is a separate entity in Power BI.

Now, instead of loading them one by one, this Parameter feature allows you to store the source path as a static, fixed parameter (which you can change at any time by your convenience) and then select/import each file with data as a separate table entity into the Query Editor.

This improves the refresh time of your report because the path is a parameter, and it loads only once throughout the reports. Selecting the path manually for each file makes the report refresh heavy because Power BI has to navigate to that file path every time it is being used as a source for each file. Which takes a lot of time and eventually slows refresh down.

To Demonstrate this further, suppose you also have the Sales Persons data that you believe can also be helpful in analyzing the sales further.

Now, instead of going back to the Get Data menu and repeating these steps, you can do this instead.

1. Make a copy of the Sales_Table query and rename it as Sales Persons (or you can create a blank query and copy the M-Code from the Source step of Sales_Table).
2. Inside the M-Code, change only the part that is in double quotes. It is the file name. You change it to “Sales Persons.xlsx.”
3. Navigate to the SalesPerson table from that source Excel sheet.

Whoa! Your Sales Persons data is in Power Query Editor for further processing.

That makes things a lot easier than manually selecting/changing the file path every time. doesn’t it?

Conclusion

Changing data sources in Power BI is a prevalent yet underrated task, and people rarely pay attention to it. But if done precisely, it can save you a lot of data refresh time when working with heavy data source files.

Through this article, we saw six different ways of changing the data source in Power BI. Five ways are executable in Power Query Editor, and it makes things easier because you can have concise data moving to the Power BI for Modeling and Visualizing purposes.

1. The Data source setting method allows you to browse the data on your own by navigating towards the folders where it resides, and it is the easiest way to change the data source.
2. Changing the M-Code in the formula bar requires you to alter the static file path (mentioned in double quotes) to the new location where the source files are now moved.
3. The same thing can be achieved through the Advanced Editor functionality of Power Query Editor, and you can replace the old static path with a new one.
4. You can use the Edit Settings option from the Source step in Power Query Editor to browse through the new file path and change it altogether.
5. The fifth way is the light version of data source parameterization. In this method, you store the data source path in an Excel cell and then use that cell inside Power Query Editor and force your Source query to pick the data file from there. It is very similar to the Data Source Parameterisation. But has it’s own benefits and challenges altogether.
6. The last way is by far the time-saving of them all and it just needs you to set your Data Source path as a static parameter and then provide the file names as an extension to it to allow your Source queries to fetch data from there.

These were the six different ways discussed in this article to change the data source in Power BI. Do you know any other way that can be used to save time and change the data source in Power BI? Do let me know in the comments.