Using a Txt or XML-File as source-path-replacement

[Anonymous]

Hi everyone,
I'm trying for days now ..

I have to figure out how to make our pbix files neutral.
Leaving everything as it is, except the https-connectionstring. I found the hint here using a xml-file but I really don't know how to use it. 
Did anyone face the same issue? 

My source looks like this before the changes:

let

    Source = Json.Document(Web.Contents("https://xxx.xxx.com/xxx/api/xx/xxx/")),

    #"In Tabelle konvertiert" = Record.ToTable(Source)

in

    #"In Tabelle konvertiert"

And I would like to use it like this, so that we could just save a file for every customer:

Source = File.Contents("C:\Users\xxxx\x\Dokumente\Config.txt")
or
Source = File.Contents("C:\Users\xxxx\x\Dokumente\Config.xml")

Power BI can open the file but that is not what I need. I want it to use the content and replace the space after Source.


Please excuse my bad English. I hope someone can help.

Thank you in advence
P.S. I am not the one working with Power BI. Just the person who has to figure out how to fix this problem.

[Anonymous]

Hi @Anonymous ,

Yes, it is possible. You can direct refer value from another query table.
BTW, power query tables not support dynamic data structure, you need to confirm they have same structure before change connection string.

Reference:

Change pbix data source programmatically

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @Anonymous ,
Thanks for the quick answer. 
Unfortunatly I am missing a few informations to understand and replicate this solution. 

You reffered to an older post, which I already tried... 

What does this xml has to look like?

I cannot use "Source" twice in my code... can I?

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=",""),";"),
    Name= List.First(connectString),
    ConnectString= List.Last(connectString)),
    Source= //invoke data connection method here       ----- 
in
    Datasource

Thanks in advance

[Anonymous · Accepted solution]

Hi @Anonymous ,

Current query table not allow you to use same step name, I share the test query below.

let
xmlTable = Xml.Tables(File.Contents("C:\Users\xxxxx\Desktop\test.xml")),
server=xmlTable[Server]{0},
database=xmlTable[Database]{0},
Source= Sql.Database(server,database)
in
Source

Xml:

<config>
<Server>"abc"</Server>
<Database>"edd"</Database>
</config>

In addition, can you please share your xml data structure so that I can modify my query to analysis correspond parameters.

Regards,
Xiaoxin Sheng

[Anonymous]

Hi @Anonymous ,

Now I got it! Thank you very much! 

My xml-structure isn't more than this. But it works just fine 🙂 
To use the URI you have to write it without ""

<config>
<Server> https://connection.com/xxx/xxx/ </Server>
</config>

Thats the Power Query

let
xmlTable = Xml.Tables(File.Contents("C:\Path\Config.xml")),
#"Changed Type" = Table.TransformColumnTypes(xmlTable,{"Server", type text}), 
server = xmlTable[Server]{0},
Source = Json.Document(Web.Contents((server)))
in
Source

Regards
Kathrin