Forum Discussion
Iterating / changing query parameter to build a merged table
Hi, I'm new to PowerBI (1-2 hours playing around, no background reading) but very impressed so far.
Hopefully someone can give me a pointer with my first PowerBI challenge...
I am using a web data source to get XML from a 3rd party API.
The API allows me to fetch a list of 'workstations', but I must specify a single 'clientid' as a parameter in the API URL.
It only returns the workstations for that one clientid.
To get 'all workstations' for 'all clients', I need to run multiple API calls changing the clientid parameter each time.
Here's how I'm getting a list of clientids:
= Xml.Tables(Web.Contents("https://foo.com/api/?apikey=xyz&service=list_clients")){0}[items]{0}[client][clientid]Here's how I'm getting a set of workstations for a given clientid:
= Xml.Tables(Web.Contents("https://foo.com/api/?apikey=xyz&service=list_devices_at_client" & "&clientid=123456")){0}[items]{0}[client]{0}[workstation]
...but what I really want is to iterate over a list of all those clientids and build a single 'merged' table of all workstations.
For bonus points I would like a custom column in that merged table that shows the clientid for each row.
I've tried using parameters in my workstation query, and even succesfully turned it in to a function, but I am struggling with iteration/merging data automatically.
Grateful for any help.
Well done! So if you have the function already, all that's left to do is to
1) transform your list of ClientIDs to a table (tab: List Tools -> Convert -> To Table) and
2) add a column (tab: Add Column -> General -> Custom Column
3) In the dialogue, call you function (for each row), passing [Column1] as the parameter that holds the Client ID
4) Expand that column
6 Replies
So, most likely, you are going to need to build a function in M which you would then call as you interate over your List of client ids. MarcelBeug or ImkeF are probably the best people to help out with that.
If you want some basics of how this works, have a look at this article:
Well done! So if you have the function already, all that's left to do is to
1) transform your list of ClientIDs to a table (tab: List Tools -> Convert -> To Table) and
2) add a column (tab: Add Column -> General -> Custom Column
3) In the dialogue, call you function (for each row), passing [Column1] as the parameter that holds the Client ID
4) Expand that column
Thank you so much ImkeF :smileyvery-happy:, very close to a solution now...
Some of the fields in the new column show as Error (because there are no workstations):
"Expression.Error: There weren't enough elements in the enumeration to complete the operation."
I therefore cannot expand the column yet.
Does this need to be fixed in the code for my Function (so that the function always returns someting even if there are no workstations?)
Or is there something I can do in the Table.ExpandTableColumn code to deal with that case?
Thanks,
Richard
Easiest would probably be to use an error handler: try YourFunctionCall otherwise null
This would make the code of that step look like this:
Table.AddColumn(YourPreviousStep, "Custom", each try YourFunction([Column1]) otherwise null)
