cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rmjersey Frequent Visitor
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Iterating / changing query parameter to build a merged table

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
Super User
Super User

Re: Iterating / changing query parameter to build a merged table

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...

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Iterating / changing query parameter to build a merged table

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

rmjersey Frequent Visitor
Frequent Visitor

Re: Iterating / changing query parameter to build a merged table

Thank you so much @ImkeF Smiley Very Happy, very close to a solution now...

 

Some of the fields in the new column show as Error (because there are no workstations):

Untitled.png

"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

 

Super User
Super User

Re: Iterating / changing query parameter to build a merged table

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Iterating / changing query parameter to build a merged table

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.

🙂

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




v-huizhn-msft Super Contributor
Super Contributor

Re: Iterating / changing query parameter to build a merged table

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 349 members 3,123 guests
Please welcome our newest community members: