How to change Power Query source and keep all steps and queries etc

[Nietzsche]

Hello, I am currently reading a book called Mastering Microsoft Power BI by Deckler and Powell.

The book comes with PBIX files hosted on github that's connected to AdventureWorks files that's located on the author's SQL Server database. 

So if you download any of the PBIX files that the author has provided you will see the data source settings like this

Since obviously I do not have access to the author's database, I downloaded the bak file provided by the author and restore it on my own SQL Server database. 

I want to change the data source for all the queries in the pbix and point them to my own SQL Server database, there are many queries as shown

Whenever I tried to change edit source, it just ask me for creditial to the author's database which I obviously do not have. 

So is there a way to change data source for any of the queries? If so, can do I once for all of them?

[Nietzsche]

awesome, thank you for your fast and detailed replies. You're an asset to the community.

[Anonymous]

@Nietzsche ,Glad to help!

You can try to see if opening the editor in power query to see if the current data source is the original data source

Here are the results of my test:

The demo here is 2017,I'm going to change it to 2019, both databases have this table.

Then modify it to your own data source
like this:

AdventureWorks2017 = Source{[Name="AdventureWorks2019"]}[Data],

There is no error even after modifying the data.

So you can modify the data into your own data source, please ensure the consistency of the data, and can correctly provide the access credentials of your own database.

I hope my suggestions  give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nietzsche]

I think I figured it out how to replace the data source.

Does that mean I have to change the data source one by one in Advanced Editor for all queries involved? 

[Anonymous · Accepted solution]

Hi @Nietzsche ,I am glad to help you.
Unfortunately, if you have more than one data source, you will need to modify each one of them.
If what I provided above about modifying data sources in power query works, for your second question: if your report relies on several different data sources, you could modify them one by one in the query editor of power query. You could also create parameters and use them instead of complex data sources.

Here are my test results.

The advantage of creating parameters is that you can centralise the management of parameters, and in the future if your own data source changes, you only need to change the value of the centrally managed parameter, which saves management time. However, a parameter generally represents a path to a data source, so you will need to create multiple parameters to manage.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.