cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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

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.

 

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

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
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors