Reconnect PBIX file to another SQL server/instance

[Anonymous]

Hi folks!

As always, I need your wisdom.

I've done a Power BI report with my PC and direct query connected to a SQL server (Business Central ERP, if it can help) called "XXXXXX".

I need to open it in my company's desk with another SQL Server, and another instance. It's easy, I just need to select "Transform data - Data source settings" and it works perfectly. (Green colour in the example)

So, where is the problem?

Here is and example. I select "Advanced Editor" in the Power Query editor, and I need to change "CRONUS España S_A_" for another name (in this case, it's the company name) for EVERY table. 

Is there a way to automate this action? It's really inefficient changing it table by table.

let
Origen = Sql.Database("XXXXXX\BCDEMO", "Demo Database BC (14-0)"),
#"dbo_CRONUS España S_A_$Item Analysis View Budg_ Entry" = Origen{[Schema="dbo",Item="CRONUS España S_A_$Item Analysis View Budg_ Entry"]}[Data]
in
#"dbo_CRONUS España S_A_$Item Analysis View Budg_ Entry"

Sorry for my english 🙂

Thanks a lot for your help. I really appreciate that.

[Greg_Deckler]

Create a query parameter and use that. Won't help now but will in the future.

[Anonymous]

Thank you for your answer @Greg_Deckler 

The thing is that I don't know how to do this, because this information doesn't exist in any table. Is a piece of the name of the table.

[Greg_Deckler]

Right, so I believe this would work. You will have to play with the quoting most likely. @ImkeF may know how best to do this.

let
Origen = Sql.Database("XXXXXX\BCDEMO", "Demo Database BC (14-0)"),
#"dbo_  & TableNameParam & $Item Analysis View Budg_ Entry" = Origen{[Schema="dbo",Item="CRONUS España S_A_$Item Analysis View Budg_ Entry"]}[Data]
in
#"dbo_CRONUS España S_A_$Item Analysis View Budg_ Entry"

[Anonymous]

I'll wait a couple of days. I hope your mate @ImkeF is going to try to solve this issue!

If not, i'll post this last capture as a question.

Thank you again, appreciate that 🙂

[ImkeF · Accepted solution]

Hi

you integrate parameters with escaping the text and an ampersand - at both sides: " & YourParameter & "

So please try it like so:

let
Origen = Sql.Database("XXXXXX\BCDEMO", "Demo Database BC (14-0)"),
#"dbo_" & YourParameter & "$Item Analysis View Budg_ Entry" = Origen{[Schema="dbo",Item="" & YourParameter & "$Item Analysis View Budg_ Entry"]}[Data]
in
#"dbo_" & YourParameter & "$Item Analysis View Budg_ Entry"

[Anonymous]

AMAZING! Thank you!

I had to do a little change, the parameter wich is in the right of the example, is the only one that needs quotation marks and space between the parameter and the "&".

The correct way is:

let
Origen = Sql.Database("XXXXXX\BCDEMO", "Demo Database BC (14-0)"),
#"dbo_&YourParameter&$Item Analysis View Budg_ Entry" = Origen{[Schema="dbo",Item=""& YourParameter &"$Item Analysis View Budg_ Entry"]}[Data]
in
#"dbo_&YourParameter&$Item Analysis View Budg_ Entry"

Appreciate your help a lot 🙂