Iterating / changing query parameter to build a merged table

[rmjersey]

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.

[ImkeF · Accepted solution]

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

[rmjersey]

Thank you so much @ImkeF , 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

[v-huizhn-msft]

Hi @rmjersey,

Have you resolved your issue? If you have, welcome to share your own solution, or mark the right/helpful reply as answer. More people will learn new things here.  If you haven't, please feel free to ask.

Best Regards,
Angelia

[ImkeF]

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)

[ImkeF]

BTW: You can also iterate a list "the traditional way" using List.Transform: https://msdn.microsoft.com/en-us/library/mt253641.aspx

But the advantage of the Table.AddColumn-method in my eyes is that the ClientID is kept in a separate field.

🙂

[Greg_Deckler]

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:

https://community.powerbi.com/t5/Community-Blog/Using-Recursion-to-Solve-Hex-to-Decimal-Conversion/b...