How to change data sources

[Lora_0121]

Hello.

I am currently working on a report using an Excel file.
I built a data mart to automate data refresh and I'm using DirectQuery mode to import the data.
If the data source changes, do I have to recreate the report from scratch?
Is there a way to replace only the data source?
The data structure is the same and the column name is the same.

I'd appreciate it if you could provide me with related documents as well.

[v-echaithra]

Hi  @Lora_0121 ,

Thank you @maruthisp , @MasonMA , @Aburar_123 , @Irwan 

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.

[maruthisp]

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

[MasonMA]

@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:) 

[Aburar_123]

Hello @Lora_0121 ,

you can follow the below steps,

Table1 : Connected to Excel (already existing)

Table2 : Connect your datamart that is having the same structure with Table1

Now replace the Table1's Power query with Table2's Power query in Advance editor window.

Finally Delete Table2 and Close & Apply

[Irwan]

hello @Lora_0121 

i usually did like below.

1. Original Data

2. Delete Original table

3. Rename the new table as the original table

Hope this will help.

Thank you.