Replace Datasources in Excel

[upfaffer]

Hello,

we have many Excelsheets using different Datasource especially SQL Databases and Qubes.

We'd like to replace this connections in Excelsheets with Powerbi Workspaces Contents (Dataset, Reports) without redesign the Excelsheets and create to much effort. What is the best way to handle this?

Example, we create for each SQL Database a dataset in Powerbi Workspace, with the same configuration, structure and content.
When we try to add this in addtion (How to for direct replace is not know), it shows only a insert option of Pivot not the table structure (columns). Any suggestions?

[vojtechsima]

Hi, @upfaffer 
Create a Dataflow in your workspace, put all your data there and then use this dataflow as the source for your reports.

[upfaffer]

The question is just how to replace the data mapping in Excel? Your answer do not fits this request

[vojtechsima · Accepted solution]

@upfaffer 
I suppose, if you have the data loaded through Power Query in Excel, you can just copy the Queries or their Code from Advanced Editor and paste it into the new Dataflow's power query window and all steps will be done in the dataflow, given the Dataflow and its Gateway can see on that file.

[upfaffer]

Could you share more details or perhaps a howto description from the internet?

[vojtechsima]

Hi, @upfaffer 
something like this:

Here is how to do it from Excel:
https://docs.microsoft.com/en-us/power-query/dataflows/how-to-migrate-queries-from-power-query-deskt...

https://www.youtube.com/watch?v=kEhUGUWQoGw