Dynamic datasource for Sharepoint

[Oimat]

Hello, 

I manage 70 different Sharepoints. That list can vary each Sharepoints year, but they are all formatted the same. I do not wish to make 70 different queries and manage that by hand every time a new Sharepoint is added or an old one is removed.

There is a Sharepoint List with the URL lists of all Sharepoints. Here is the simplified query : 

let
    Source = SharePoint.Tables(Source_List, [Implementation=null, ApiVersion=15]),
    // Do something on the Source to get the right list of Sharepoint URL in [LienCONTRIBUTIONS_RI.Url]
    #"Recupération Sharepoints" = Table.TransformColumns(#"Lignes du haut conservées", {{"LienCONTRIBUTIONS_RI.Url", each SharePoint.Tables(_, [Implementation = "2.0"]), type table}}),
    #"Récupération type/items" = try Table.ExpandTableColumn(#"Recupération Sharepoints", "LienCONTRIBUTIONS_RI.Url", {"Title", "Items"}, {"Sharepoint Data.Title", "Sharepoint Data.Items"}) otherwise null
in
    #"Récupération type/items"

Of course, there is a problem of dynamical datasource. Each Sharepoint asks for different credentials (even though they are on the same Sharepoint hub, it could be just one!!).

I tried :

• AddColumn,
• TransformColumns
• Loop
• Writing a table with "Insert data" instead of fetching from a Sharepoint List
• Creating a parameter with the url lists instead of fetching from a Sharepoint List

From what I've seen from the other posts, the problem is that my sources need credentials, compared to other topics.

Any idea, or should I really do 70 different dataflows ? Also I want different to extract 3-4 lists of each sharepoint site and the documents, so that would be like 300 queries...

Thank you !

[Joe_Barry]

Hi @Oimat 

To solve the issue with credentials, ask the IT department to create a service account for you for BI purposes, this should be used instead of personal accounts. You will need to ask the SP site owners to give this service account access to all lists.

Bringing in 70 lists into a PBI report will cause many issues. If you have PBI or Fabric premium, why dont you create 7 data flows, add 10 SP lists to each. Set them all to unabled and since they are all formatted the same, then append them alltogether into one enabled table. When you have created the 7 dataflows, create an 8th Dataflow that refrences the 7 othersand append them together into one table, which you will use in the PBI Report.

The reasoning with spreading the Lists out over 10 dataflows is that the refresh would take forever if there were more. Be aware that 10 could be also too many, so test it.

Joe

[Oimat]

I already have a service account, and I still have to connect 70 times for each different Sharepoint.

Refreshing time is not an issue, as it is pretty fat (~3 minutes). But maintaining the list will becomre very tedious and technical.

Whenever a new site is opened or an old one is decomissioned, there would be a need to change the power query code. It would be so much easier to change a column in a Sharepoint list 😞

[v-nmadadi-msft]

Hi @Oimat ,
Ensure that all SharePoint sites share the same credentials by centralizing authentication. If possible, assign a common service account with the necessary permissions across all sites. This approach simplifies access management and avoids authentication issues when dynamically connecting to multiple SharePoint sources in Power BI.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[Oimat · Accepted solution]

Sadly it is not in my power to do that 😞

I ended up using a Python script to generate 70 lines of query and appending all the tables... We'll have to update by hand when the list changes.

[v-nmadadi-msft]

Hi @Oimat ,
We really appreciate your efforts and for letting us know the update on the issue.
Please consider accepting your reply as solution so as to help other community members in the future who may face similar issue. 

Please continue using fabric community forum for your further assistance.
Thanks and regards