cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Loop and union of data from webservice based on secondary table

Morning,

I hope someone can help me with this.

I am attempting to pull and merge/union a number of datasets based on another list of ID's

So the first query would be something like the following:

LCHolmes_0-1603882361939.png

This gives me my list of id's:

LCHolmes_1-1603882462919.png

I need then to loop through and make calls to the webservice based on the ID:

LCHolmes_0-1603883848695.png

 

 

and union it all together as unfortunately the webservice doesnt have a call for give me all absensces for everyone.

Any help, guidance or ideas on this would be much appreciated as I am relatively new to power query and not to sure where to start with this.



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hello @LCHolmes 

 

so add a new column to your id-Table where you calling this webservice like you did in you last post. Substitute the "ID"-part with the column reference as I was showing in my previous post. 

Then you should get a table in every cell of your new column. Then you can combine this table-column with Table.Combine(YourPreviousStep[YourNewColumn])

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

4 REPLIES 4
Highlighted
Super User II
Super User II

Hello @LCHolmes 

 

you can add a new column where you making the data access and use the ID of parameter of it lik

Web.Contents("httpxxxxxxx/" & _[ID] & ")")

Depending what this web service is handing over, you can combine the results like

Combine = Table.Combine(YourPrevisousStep[YourNewColumn])

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Highlighted

Thanks for your reply @Jimmy801 .

 

Apologies though, I'm new to this language and don't really understand what you mean.

When I make the call to the webservice with the ID included i get the following back:

LCHolmes_0-1603884496635.png


So I need to make that call for each uniqueID from the initial request and union them.

 

Highlighted

Hello @LCHolmes 

 

so add a new column to your id-Table where you calling this webservice like you did in you last post. Substitute the "ID"-part with the column reference as I was showing in my previous post. 

Then you should get a table in every cell of your new column. Then you can combine this table-column with Table.Combine(YourPreviousStep[YourNewColumn])

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Highlighted

Thanks for that.

This is what I have ended up with, while it might not be the best its working and I can tweak as I learn more

LCHolmes_0-1603887914165.png

 

Thanks for the help and the extra explination @Jimmy801 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors