Power BI is a powerful business intelligence tool that allows users to:
- connect to multiple data sources
- transform data
- create interactive reports and dashboards
Power BI helps to make data-driven decisions. It allows users to explore their data and create compelling visualizations. Power BI also makes it easy to collaborate on projects. This is done by allowing users to share reports and dashboards with colleagues or customers.
With Power BI, you can unlock the power of your data, gain valuable insights and make more informed decisions.
Changing Your Power BI Report’s Data Source Comes with Its Own Set of Challenges
However, there may be situations where the data source location changes. Or you need to connect to a different data source, such as when moving from a test environment to a production environment. In these cases, you need to change the data source for the existing Power BI report.
This is not an easy task as it requires you to manually update all the necessary information, such as:
- the data source location
- user credentials
- data sources connection strings
- table and field names
Fortunately, there are some ways to make this process easier.
This blog will walk you through the process of how to change the data source for existing Power BI report.
Note: Microsoft AdventureWorks sample database used for this exercise can be found here.
Sections
How to Change Data Source for an Existing Report
There are two ways of changing data source for existing Power BI report. These include:
- Data Source Settings
- M Code
Data Source Settings
This can be used to change the data source for the same source type. You can change the data source using data source settings in two ways:
- Using File
- Using Transform Data
The following steps outline the process of changing the data source for an existing report using Data Source Settings:
Using File
Step 1: Open the Power BI report in Power BI Desktop.
First, you will open the Power BI report that you want to update in Power BI Desktop.
Step 2: Click File.
Step 3: Click Options and settings from the left pane and then Data source settings on the right pane.
Step 4: Click on the data source you wish to modify and then choose “Change Source.”
In the โData source settingsโ window, you should see a list of all the data sources that are currently used in the report. Select the data source that you want to change.
Step 5: Click Browse to select a new data source Excel file if in local storage or past file path in the box if in the cloud and then click OK.
Step 6: Click Close.
Step 7: Apply changes.
Using Transform Data
Step 1: Open the Power BI report in Power BI Desktop.
First, you need to open the Power BI report that you want to update in Power BI Desktop.
Step 2: Click Transform data drop-down and then Data source settings.
Step 3: Click on the data source you wish to modify and then choose “Change Source.”
In the โData source settingsโ window, you should see a list of all the data sources that are currently used in the report. Select the data source that you want to change.
Step 4: Click Browse to select new data source excel file if in local storage or past file path in the box if in the cloud and then click OK.
Step 5: Click Close.
Step 6: Apply changes.
M Code
Besides changing the data source for an existing Power BI report using the user interface, it is also possible to modify the data source using M code. M code is the programming language used to transform and shape data by Power Query.
To change data source with M code, replace existing details with new ones for the desired data source. You can use this to change the data source for the different source type.
The following steps outline the process of changing the data source for an existing report using M code.
Step 1: Open the Power BI report in Power BI Desktop.
First, you need to open the Power BI report that you want to update in Power BI Desktop.
Step 2: Click Transform data.
Navigate to the โDataโ tab, click the Transform data drop-down, and then Transform data to get to Power Query.
Step 3: Click on New Source and connect to the new data source. Confirm the column names in Table 1 (new data source) are the same as that of the Sales table (current data source).
Step 4: Click on Advanced Editor.
Select Table 1, navigate to the โViewโ tab, and click on Advanced Editor.
Step 5: Copy the M code up to [data].
Locate the M code that specifies the new data source and copy from beginning up to [data].
Step 6: Select Sales table and click Advance Editor. Replace the M code up to [data] with the code you copied from Table1 and click done.
After making the necessary changes, the user should review the M code and ensure it accurately reflects the new data source.
Step 7: Delete Table1 and click Close & Apply.
This has successfully changed the data source.
Key Takeaways
Changing the data source for a Power BI report is significant. It ensures that the report always delivers accurate and relevant insights. Even if the underlying data changes or relocates to a different location, the report remains dependable.
To connect to a new data source, you can update the connection details effortlessly. Doing so will guarantee that your report always uses the most current data available. This is especially useful when moving from a test environment to a production environment. This is because it maintains data analysis and reporting capabilities.
Power BI offers tools to work with various data sources and manage connections across environments. These include connectors and data transformation tools to clean and reshape your data before using it in your reports.
You can keep your reports accurate, relevant, and up-to-date despite any changes in your data sources or environment. This is achievable by following the steps outlined above.
If you have more questions about changing data sources in this tool, please consult the Power BI Community for help.
Boost the value of your data and make smarter business decisions by connecting with Iteration Insights today. We are a team of experienced professionals who can help you access and leverage your data to uncover valuable insights. Our team has expertise in Power BI, data and analytics, visualization, and more!