Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
@Cadker not sure it would work but can you please untick the following and try
No luck. For now I just hardcoded the Authorization header into it so I dont have to enter my credentials 50+ times