Change Data Sources for Existing Power BI Reports

Change Data Sources for Existing Power BI Reports using M Code

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.

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, navigate to the “View” tab, and click on Advanced Editor.

Locate the M code that specifies the data source. 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.