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 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 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 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 new file also. 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 >> open your PBIX file >> From Home tab expand "Edit Queries" >> Click on Data Source Settings
You will see your data source and "Change source" button >> Click on that button to change your data source.
Click on Browse button and Select your new data source file >> Click on Close >> Click on Apply Changes from Yellow ribbon bar >> Check if all visuals are working fine >> if not then again go to Edit Queries, find errors and fix them.
Use different data source connector – 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 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 ribbon menu click on "Advanced Editor"
You should see Power Query Formula language "M" - mashup code as shown below; this is your data import and transformation code. We can either replace 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 do 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 - 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 query you will see M code for your new data source, you can copy this and use 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 mismatching 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 want to replace with a similar data source or 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.