How To Change Data Source Of Existing Report In Power BI

Problem

If you have created a Power BI report then you know that you need to select a data source at the beginning while creating a report. You have created all your visuals and pages and published a report to the Power BI service.

Now you want to replace or change your data source for some reason it could be that you need to connect your report to a live data source, or it's just a different Excel file or text file, or a different table in a different database or a different list in a different SharePoint site.

How can you do that? Is it possible? What are the different options to achieve it?

Solution

There are two ways by which you can achieve it. Depending on the type of data source you want to replace you can use one of the below approaches.

Replace the data source using the same connector

If you have created your report connected to an Excel file/CSV or text file and you want to replace that with some other file, then you can use this approach. Follow the below steps to do it.

Note. You must make sure that all columns used in the reports or used in modeling are present in the new file. Otherwise, you will get errors after you change the data source which in any case you can fix by working around it with data transformation steps.

Open your Power BI Desktop, and Open your PBIX file, From the Home tab expand "Edit Queries" and Click on Data Source Settings.

You will see your data source and the "Change source" button Click on that button to change your data source.

Click on the Browse button and Select your new data source file, Click on Close, Click on Apply Changes from the Yellow ribbon bar, and Check if all visuals are working fine, If not then again go to Edit Queries, find errors, and fix them.

Use different data source connectors as data sources are completely different.

You have created a report connected to an Excel file. Now you have decided to convert that Excel file into a SharePoint online list for better data management. So, you need to replace your report data source also from the Excel file to a SharePoint list. How to do this? The previous approach doesn’t work in this scenario. So, we need to work at the code level.

Check the below steps.

Open Power BI Desktop, Open your PBIX file, Click on Edit Queries.

You should see a preview of your data as shown below, From the right side window of Query Settings, select Source, From the ribbon menu click on "Advanced Editor".

You should see the Power Query Formula language "M" mashup code as shown below; this is your data import and transformation code. We can either replace the complete code from this window with new data source code or just replace the first part which is related to the source of data as highlighted.

I recommend if you haven’t done much of the transformation, or it is easy to do the transformation again on a new data source then it's better to replace the complete code.

When I say replace with new code of new data source how can you get this new code? It's easy. Just start another session of Power BI Desktop, start a new report, connect to your new data source, edit data or edit query, copy M code from there, and use it above to replace.

E.g. below is my new data source.

The SharePoint online list

I am connecting to my new data source "SharePoint online list" in a separate session of Power BI Desktop.

Once you edit the query you will see M code for your new data source, you can copy this and use it in your earlier report

Click on Done, Apply Changes, and check if all your visuals are working fine or not.

If you have applied a lot of transformation steps, and don’t want to repeat those again, then you can replace only the data source code.

If data types are mismatched between your old and new data source, then you might need to transform your data. Make sure the variables are used correctly, #“VAR” is the table variable on which we do transformation operations.

Summary

It is possible to change the data source of your existing report in Power BI Desktop. Make sure you replace it with a similar data source or a completely different data source and choose the correct approach accordingly.

Hope this helps! Thanks for reading. Please comment below if something is not correct in this article or if you know of a better way of achieving this.