Forum Discussion

suvaries's avatar
suvaries
Frequent Visitor
9 years ago
Solved

Problem while publishing a file with parameterized connection properties

Hi, I created a .pbix file for our customers. Each customer has its own database, so to be able to use the same file they have their own config.xml file. The powerbi desktop file gets the connection...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi suvaries,

 

Based on test, I can't reproduce your issue, can you share us some detailed information about this issue?

 

Query:

let
    Source = Xml.Tables(File.Contents("C:\Users\xxxxx\Desktop\Config.xml")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:name", type text}, {"Attribute:connectionString", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute:connectionString", "ConnectionString"}}),
    Custom= List.First(Table.SelectRows(Table.SelectColumns(#"Renamed Columns","ConnectionString"), each [ConnectionString] <> null)[ConnectionString]),
    connectString=Text.Split(Text.Replace(Text.Replace(Custom,"server = ",""),"data source=",""),";"),
    Datasource= SalesRecords(List.First(connectString),List.Last(connectString))
in
    Datasource

 

 

Screenshots:

 

 

 

I tested on Version: 2.39.4526.362 64-bit (September 2016).

 

Regards,

Xiaoxin Sheng

  • suvaries's avatar
    suvaries
    Frequent Visitor
    9 years ago

    Hi Anonymous

    Thank you for your attention.

    My query is as follows. "sir" is a database table in my mssql database.

     

    let
    configPath = "F:\...\Desktop\config.xml",
    Source = Xml.Tables(File.Contents(configPath)),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"server", type text}, {"database", type text}}),
    Table1 = #"Changed Type"{0},
    srvr = Table1[server],
    db = Table1[database],
    databaseCon = Sql.Database(srvr, db),
    dbo_sir = databaseCon{[Schema="dbo",Item="sir"]}[Data]
in
    dbo_sir

     

    My config.xml file is,

     

    <root>
  <connection>
    <server>localhost</server>
    <database>dbTrial</database>
  </connection>
</root>

     

     

     

    Query result is as I expect.:

     

     

    Desktop file works fine.

     

    And I publish,

     

    In desktop file I get this error:

     

    Meanwhile in service I get this error:

     

    We are exactly in the same version,

     

    An interesting thing is that I have also a direct-mode file with similar structure. I am not having any trouble while publishing it(even though it seems to connect more than one datasource, an sql server and an xml file)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi suvaries,

       

      >>Meanwhile in service I get this error:

      Did you use the directquery mode to connect the database? If as I said, you need to add a data gateway to connect the database.

       

      For more information, you can take a look at below links:

       Use DirectQuery in Power BI Desktop

      On-premises data gateway

       

      >>An interesting thing is that I have also a direct-mode file with similar structure. I am not having any trouble while publishing it.

      I think these success published reports may use the import mode to connect, it not need a gateway to connect and refresh the data.

       

      Regards,

      Xiaoxin Sheng

      • suvaries's avatar
        suvaries
        Frequent Visitor
        9 years ago

        Hi Anonymous

         

        >>Did you use the directquery mode to connect the database?

        No, my workbook imports the data.

         

        Thank you for your efforts.