How to change data sources

Lora_0121 

Hi,

For your first question, No, you don't need to recreate your report from scratch when your Excel data source changes location or gets updated;

2nd, a better way to update data source would be using parameters to define the data source path if you plan switching sources in future, or you can also refer to this video from Paragmatic Works showing how to change data source by updating M code in Power query. 

https://youtu.be/Fd2tU-qull8?si=x6gPXRpHsyUsdS-h

In case you would like to use Parameter to manage your data source in future, here's a document from Microsoft about how to build parameter in PQ, which also applies on Data source Path. 

https://learn.microsoft.com/en-us/power-query/power-query-query-parameters

I assume the logic in Power query would be 

let
Source =
if SourceType = "Excel" then
Excel.Workbook(File.Contents(DataPath), null, true)
else if SourceType = "Datamart" then
Sql.Database("sql_server_name", "db_name")
in
Source

Hope it helps:) 

Hi Lora_0121,

Below are the approaches which I would suggest as you are using excel sheets as source.

1. Change the Change Source in the Transform data --> Data Source Settings, here please update server/database/filepath

2. Create 2 parameters, one for Servername and second for DatabaseName as TEXT and pass the values if you are using SQL Server database.

3. Once you deploy the pbix file into Power BI service, then two types of files will be created. one is Report and second is Senamtic Model. So do the changes in the Semantic model to repointing to new datasource. After doing the changes, just refresh the Report which is pointing to the Semantic Model which you modified as a source.

Please let me know if you have any further questions or need clarifications.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X