Forum Discussion
Reconnect PBIX file to another SQL server/instance
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.
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"
6 Replies
Create a query parameter and use that. Won't help now but will in the future.
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.
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"
