Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rmjersey
New Member

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
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 

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

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)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.