Power BI is highly dependent on being able to follow a set series of instructions. We’ll explain how to change Power BI Data Sources seamlessly to minimize errors and the need to update DAX formula when swapping out one source of data for another.
We’ll keep things pretty straight forward and show you the best way to change data sources when converting between two Excel files, and then look at best practices to avoid having to update all of your DAX formulas when updating a data source.
Let’s jump in!
Table of Contents
When to Change Data Sources in Power BI
The need to change data sources in Power BI often comes up in a number of common scenarios. It’s a useful trick to have in your bag whether you are a Power BI Developer that works in house or if you’re an outside consultant and building a dashboard on behalf of a customer.
Some of the most common scenarios that require changing a data source in Power BI include:
- Developing with a flat file and switching to a live connection
- Change in source system, ERP, or accounting software
- Sharing .pbix files between users with different relative source file locations
- New Power BI developer updating an existing report
These aren’t necessarily inclusive of every reason that you would need to change a data source in Power BI but probably covers at least 80% of use cases that we see.
Now, let’s take a look at how to Change a Power BI Data Source.
How to Change Data Sources in Power BI
If you’re changing data sources in Power BI from one Excel file to another or from CSV to CSV where the column headers are all the same, it’s very simple. The quickest way to make the change is from the Report View, go to Transform Data, Data Source Settings, Select Change Source, and update the File Path or choose a new file.
Here’s how in more detail.
1.) Select Transform Data, and go to Data Source Settings.
2.) Change Source from Data Source Settings with the .xlsx or other file selected.
3.) Assign a new file as the data source using the Browse button.
After updating the file path, click on OK and Close
Once you close out of the select file screen, you’ll see a yellow bar at the top of your report, click on Apply Changes and your dataset will be refreshed.
This method is by far the easiest and fastest, but will only work if your new dataset is the same format as the first dataset. You risk breaking DAX measures if sheet names are different or if there are changes in column names and header formats.
Let’s look at how to handle updating datasets when data is more complicated.
Changing Data Sources in Power BI with Different Formats
If your data is a different format, or if you tried the first method to update the data source and received an error, you will likely need to make data source changes in the Power Query Editor. To launch the Power Query Editor, right click on a table under the Data tab and select Edit Query or click on Transform Data, Transform Data from the Power BI Ribbon of the Report View
The Power Query view will show you the data transformations that occur when importing data under the Applied Steps section at the right side of the screen.
To change a data source from Power Query, click on Source under Applied Steps. It’s often the first step in Power Query at the top of the list. A new screen will pop up where you can select a different workbook or flat file. This method also works when updating connections to SQL, or working with data that’s been entered as a table.
If your column headers have changed from one data source to another, you’ll see an error that says “Expression.Error: The Column XYZ of the table wasn’t found.” like in the screenshot below.
There are a few ways that you can work through the error, but it’s going to depend on your specific data. Here are some general guidelines.
- Step through each Applied Step from beginning to end to identify where the error occurs. In many cases it’s the Changed Data Type step that’s applied to all columns and won’t find the previously referenced name.
- Open both files and look at them in Excel to identify where each column is different. Use a formula like A1 = A1 between each sheet to return a true / false if the column detail matches or not. Watch out for white space.
- Insert Data Transformation Steps before the error to add / remove or rename columns to match.
This is an example where we went before the Changed Type step, removed a column and renamed a column so that when we reach the Changed Type step the data is in the same format as the first input.
If you had additional steps after Changed Type Power BI wouldn’t know the difference because all of the steps afterwards will reference a column name that already exists at that point in time.
When there are complex applied steps in a query, the easiest way to swap data sets with different schemas or column names is to normalize the data to match the older query.
Updating Power BI Data Sources with the Power Query Advanced Editor
The Advanced Editor in Power BI Power Query is the line-by-line coding format of the applied steps you are used to looking at in the regular Power Query interface. It’s extremely powerful and can make short work of updating datasets.
We’ll explain how, and also point out some common use cases where this technique can be deployed.
To access the Advanced Editor, launch Power Query and from the Home Tab of the Power Query Ribbon, select Advanced Editor button.
You’ll see all of your applied steps in Power Query M code format.
Be careful when working with Power Query M if you’re new to it, it’s very easy to break and ruin all of your other applied steps.
You can quickly see though that updating a file path becomes incredibly easy as you just have to delete and re-type it to update incoming data.
Here are some tips and tricks when working with the Advanced Editor:
- Copy and Paste the Entire Query into Notepad as backup in case you break something
- Import a separate dataset, prep it, then copy and paste the code into an initial Query – The advanced editor can be used to import a completely different data source then you can copy and paste the Source lines into the original query.
- ChatGPT is your best friend – ChatGPT can write, and debug Power Query M making changes even easier than if you were to do them yourself.
- Punctuation Matters (A Lot) – Don’t forget to make sure you have the right number of commas and parenthesis. It’s one of the most common mistakes. If you get stuck, try ChatGPT.
- Find and Replace All – While not natively supported, you can copy and replace all in notepad or a different text editor then paste it back into Power BI for quick updates.
Changing data sources in the advanced editor is a great method when there’s not a direct way to update a data source or extra steps are required, such as changing a file path from your local desktop to pointing Power BI to a file that’s saved on a Share Point site.
For even more info on updating data sources, Patrick from Guy in a Cube gives some great tips and tricks.
How to Avoid the Need to Update DAX Formulas
To determine whether or not you are going to need to update all of your DAX formulas after swapping out a Power BI data source it’s important to understand the two sides of Power BI. Power Query does the initial data import, a data transformations. This step occurs when a dataset is refreshed. Then there is the report builder where DAX formulas are typed. DAX formulas are executed upon user interaction and for the reporting layer.
If you keep your data table and column names the same in Power Query after switching data sources, Power Query will refresh the data and the referenced columns in DAX will not know the difference. That’s because the data source refresh occurs before DAX attempts any calculations.
We’ll use the example DAX formula below.
DAX does not know anything about the data source Sheet1, or the column names Sales Amount (USD) or Quantity. They could come from a .csv, .xlsx a database wherever. If they are the same at the point data loads from Power Query to the report view you will not need to make any updates.
If your data changes table names or column names all of your DAX formulas will break because they won’t find the previously referenced information.
How to Update All DAX Formulas when Changing Datasets
Unfortunately, Power BI does not have a find and replace all feature for DAX formulas. If you need to update them, you have to update them one at a time. However, there are third party tools that can make this task easier to manage.
Two of the most common third-party tools designed for coding in DAX are:
These are two of the most common tools out there for people writing a lot of DAX and need help debugging it or updating it in mass. Even if you don’t need to update all of your DAX formulas at once they’re worth checking out if you’re heavy into Power BI development.
Best Practices When Changing Data Sources
Here are a few tips and tricks when you’re making changes to data sources that will make your life easier.
- Avoid making changes when possible by starting with data as close to the source as possible
- Keep table names and column names the same to avoid updating DAX formulas
- Use Power Query Parameters if you’re constantly updating file paths
- Backup your .pbix file prior to making changes to avoid permanently breaking something
- Utilize the Advanced Editor and make changes in Power Query as the preferred method
By keeping some of these tips in mind we hope that you will have to update your Power BI dashboard data files less often, but if you do then it will save you some headache!
Changing a Power BI data source can either be very fast or somewhat frustrating depending on how much of the data is changing. If you’re switching from one file to another where all of the columns are named the same the change can take a few minutes. If your data has significant changes and the columns it may be faster to map the column names from the old file to the new file and rename them, or at times it could be easier to start from scratch.
Another option is to utilize the Advanced Editor to copy and paste blocks of Power Query M code. If you get stuck or the query doesn’t work, you can try using ChatGPT to correct the code or even make changes on your behalf. Either way, make sure to keep a backup of the code just in case it breaks.
Finally, if you’re working with DAX and all of the tables and column names are the same between data sources you shouldn’t have to update anything. If things do change and all of your DAX formulas stop working, try a third-party tool to debug them or to make multiple updates at one time.