Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 t...
Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

     

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      ImkeF
      Community Champion
      6 years ago

       

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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 🙂