API calls using field as a URL parameter

[monkeysocks]

Hi,

I am new to calling API's from Power BI and would very much welcome any help I can get.

I have 2 commands that I need to run the first is:

https://SERVER/api/?apikey=yourAPItoken&service=list_clients

This gives me all the clients, next I want to know all the sites of all the clients through running the next command

The second is:

https://SERVER/api/?apikey=yourAPItoken&service=list_sites&clientid=CLIENTID

The clientid is a required parameter and is returned from the first command. I need to be able to run it for all clientid's through just the one query but I am not sure how to achieve this. Any help/direction would be gretly appreciated.

Thanks in advance,

Lara

[monkeysocks]

I have managed to resolve this issue with a function which has worked perfectly. I have repeated the command several layers down which has worked up until the last layer where the following error has occurred:

"Expression.Error: The column 'Attribute:status' of the table wasn't found.
Details:
Attribute:status"

I have gone to advanced editor and this column is present so not sure why it is producing this error.

let
Source = Xml.Tables(Web.Contents("MYURL")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Attribute:created", type datetimezone}, {"Attribute:host", type text}, {"Attribute:status", type text}}),
#"Expanded error" = Table.ExpandTableColumn(#"Changed Type", "error", {"errorcode", "message"}, {"error.errorcode", "error.message"})
in
#"Expanded error"

Does anyone have any ideas why I am getting the error despite the column being there and what I need to do to rectify it?

Any help would be greatly appreciated. Thanks in advance!

Lara

[monkeysocks · Accepted solution]

I have resolved this issue by completely removing the Change Type step