Forum Discussion
Cadker
5 years agoFrequent Visitor
Treat root domain as same datasource?
Hello,
I am doing a Web query on an API to get a list of servers as seen in the query below. I created a custom function that directly goes to each server and runs a query and it works except it sees each server as a unique source and forces me to enter in my credentials for each one and there are upwards of 50 in the list. The credentials are the same across all the servers.
Server Query
let
Source = Json.Document(Web.Contents("https://myInstance.us.domain.cloud/api/configuration/v1/servers")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "status", "address", "port", "timezone", "version"}, {"name", "status", "address", "port", "timezone", "version"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Column1",{"address"}),
#"Invoked Custom Function" = Table.AddColumn(#"Removed Other Columns", "urlQuery", each urlQuery([address]))
in
#"Invoked Custom Function"
Output from above:
address
myInstance-server-1.us.domain.cloud
myInstance-server-10.us.domain.cloud
myInstance-server-11.us.domain.cloud
myInstance-server-12.us.domain.cloud
myInstance-server-13.us.domain.cloud
myInstance-server-14.us.domain.cloud
myInstance-server-15.us.domain.cloud
myInstance-server-16.us.domain.cloud
myInstance-server-17.us.domain.cloud
myInstance-server-18.us.domain.cloud
myInstance-server-19.us.domain.cloud
myInstance-server-2.us.domain.cloud
Custom Function
(serverURL as text) =>
let
Source = Xml.Tables(Web.Contents("https://"& serverURL & "/2/query?platform=windows&query=(select%20(device_uid%20name%20last_seen%20first_seen)%20(from%20device))&hr=true&format=xml")),
Table1 = Source{1}[Table],
Table0 = Table1{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"c0", type text}, {"c1", type text}, {"c2", type text}, {"c3", type text}})
in
#"Changed Type"
Everything is under *.us.domain.cloud . Is there a way I can do it and have it treat it as one and only require credentials once?