fetch multiple odata queries into one database

[Anonymous]

Hi

I want to fetch Sharepoint User lists and security groups by using the Rest API.

The URL to the odata feed is:

http://siteURL.com/_api/Web/SiteGroups/GetById(1)

The ID (red) is dynamic. There are different IDs for each security group.  I got all IDs in another odata query.

For now I managed to set a dynamic URL connection like stated here: https://community.powerbi.com/t5/Desktop/How-to-setting-a-dynamic-URL-connection/td-p/57072

So I am able to Change the standard-value at the parameter and the Odata query (Abfrage3) changes automatically.

( eg. http://siteURL.com/_api/Web/SiteGroups/GetById(8) )

My goal is to fetch all this Odata queries into one database. (All IDs in one query).

There are more than 250 IDs.... Is that possible?

Thank you in advance!

PS: My Power Query skills are at a beginner-level... but I am learning every day 🙂

[Jimmy801]

Hello @Anonymous 

you can create a list (or a table) with all your IDs. Then you can transform the list, or add a new column to a table, where you use your ID as parameter for your GetById like List.Transform(YourList, each GetById(_))

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

[Anonymous]

Hi @Jimmy801 

and thank you for your answer. I'm afraid I would need a step by step help on this. All I have done for now is creating a List with my IDs.

Im very thankful for your help anyways.

Best,

Markus

[Jimmy801]

Hello @Anonymous 

you have to write a new function in the advanced editor like this

ExtractDataWithId = List.Transform(YourListBasicallyThePreviousStepName, each HeregetstheApicallThatUsesTheParameterOfTheList(_))

as I don't know how you are quering this api, I can't tell you exactly how to write the second parameter of List.Transform. It could also be something like this

Web.Contents("http://siteURL.com/_api/Web/SiteGroups/GetById(" & _ & ")")

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

[Anonymous]

Hi @Jimmy801 

Thanks. I think this should be correct. Altough, I do not get what the _ stands for. This should be the ID from my list? (blue mark in picture)

Or maybe I got that wrong..

Thanks for patience!

Markus

[Jimmy801]

Hello @Anonymous 

exactly... as the function transforms the items of the list, the "_" represents the list items processes. But it finally worked out?

If so I would appreciate if you could mark my post as solution

Thx

Jimmy

[Anonymous]

Hi @Jimmy801 

Unfortuanelly not yet.
Do I have to make a new query with my function as a parameter?
Or do I have to transfer the List into a table, and make a new column and invoke my function there?

What I've done so far: Right click on the List "ID" and make a function with code:

ExtractDataWithId = List.Transform(Id, each Web.Contents("http://SiteURL.com/_api/Web/SiteGroups/GetById(" & _ & ")")

But it seems not to be a function, as the symbol turns into "ABC" (A new query, not a function?) (see screenshot above)

Thanks for your help again!

Markus

[Jimmy801]

Hello @Anonymous 

this Web.Contents was just an example of mine. I don't know how to query your API. By the way you don't need to create a new query. You can make all in one like this

let
   YourList = {1,8,20,30,".."),
   ReadData = List.Transform(YourList, each YourFunctionToQuery(_))
in
   ReadData

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