Connecting to SP-Lists from multiple SharePoints - adresses comming from one Source List

[jflemmig]

Hello,

I have a set of Sharepoint-Lists, that I want to connect to to and amand afterwards. Establishing the connection on by one is clear for me. Since the amount of lists is evolving over time (new Sharepoint lists will be created on various project SharePoints) my idea was not to always connect to to the sharepoint list directly in Powerquery and amand the new tables afterwards. Instead I was thinking, if it is possible, to log the adresses of the sharepoint list in one specific adress book (could be an excel file or another sharepoint list) and from there let Powerquery connect to all SP-lists, that are included in the adressbook.

I am so far, that I have created the adressbook, connected to it and reduced the list of columns to the necessary...

This is basically my adressbook

and here how it is loaded in PowerQuery

This is the result, when expanding the Hyperlinks

...but from now on, I don't know how to proceed or if my idea is even feasable.

I am open for completely other ideas but the basic target is, that the lists Test1, Test2, Test3 and any other list, that will be added to the adressbook in future will be amanded without reastablishing a single connection through PowerQuery. The reason is, that my colleague who is managing the subject has no PRO-license, to publish the reports and I want to enable him to add a new SP-list to the reports without asking someone else in the orga with a pro license to publish the updated datamodel to the workspace.

I've been looking for ideas but the questions that I ask are mostly missleading to "how to connect to SP list" subjects. Happy if you can redirect me to an existing post concerning this question.

Thx a lot in advance for your support

Jens

[ppm1]

Please see this article. You can adapt the M code there to create a function to pull the data from multiple lists starting from an input table with site url and list name (if the lists have the same fields).

Updated – Get SharePoint List Data … Fast – Hoosier BI

Pat

[jflemmig]

Hi Pat,

thx for the link,

when I understand the code correct, it returns the content of the SP list through another way then the standard SP connector, 

My problem is, that I have no idea, how to create the respective function in order to use the hyperlinks in the adressbook to connect to all sharepoint lists included.

Basically I've never created a custom function in M 😞

BR

Jens

[christinepayton]

Yeah, what you want to be searching for is the "invoke a custom function" feature. That will let you do a series of steps on rows in a query. I believe this particular feature will not let you schedule refresh (some sort of error about dynamic content), but it's not something I use super often so perhaps someone else can chime in there. 

An alternative would be to parameterize the URL and/or list ID that you're connecting to in the query - you just add a new parameter, and replace the string for what you're connecting to with the parameter name. That would let you "hot swap" things, but you'd still be duplicating queries and appending that way. 

What you might consider is shifting the architecture of how you're storing the data to be more flexible and not so much based on creating a ton of lists, e.g. use a single Dataverse table with role-based access or something like that (though this will typically be Power Apps premium licensing). 

[jflemmig]

Hey Christine,

thx for the reply!

invoke custom functions and parametrization is indeed not something, that I am very skilled in so I would require a bit more guidance here.

the centralization of data might be an option for the future as to the data there is anyway to a powerapp connected... which is still another story, that needs to be investigated... anyway today the setup is as it is.

I thought aswell about an option to amand the tables not with powerquery but outside of powerBI... eg. with powerautomate in some kind auf summary sharepoint list... not sure if that is possible but basically it would be the same issue there. The coleague who owns the subject has no specifc licenses and I'd like to eneable him to manage the entire subject.

BR

Jens

[christinepayton]

Donnnnn't copy them with Power Automate, you'd have to build the flow to update the items every time something changes or they'll get out of sync. It'll just make your problem worse lol. 

It doesn't even necessarily have to be Dataverse, you could use a single SP list, but you don't want to be storing very similar data in a ton of disparate lists like that. It might be OK now, but like 2 years down the road you will be wishing you hadn't. 

[jflemmig]

Haha... agree

however, that it is unfortunately not in my hands. Will discuss with my colleague about the architecture of the datasource.
thx a lot anyway

cheers Jens