Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

fetch multiple odata queries into one database

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 🙂

Need Help

7 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Jimmy801
        Community Champion
        5 years ago

        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