Forum Discussion
Helper IDynamic datasource for Sharepoint
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.
Solution SageHi 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
Helper II 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 😞
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
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.
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