Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Hi @v-shex-msft ,
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
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
Hi @v-shex-msft ,
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |