Connecting Power Query to SharePoint Online list with more than 12 lookup columns

[PetterR]

Dear all,

for consolidating different data sources in a Power BI report I need to connect to a SharePoint Online list, which currently includes more than 12 lookup-table based columns (mainly people picker).

Using the standard SharePoint.Tables connector in Power Query fails due to the general 12-lookup-based-fields-threshold in SharePoint.

Do you have any ideas how to approach this issue? An idea of mine would be to use the same approach as within SharePoint with distinct views only displaying part of the entire list scope and merging the views in Power Query, however, I don't know how to connect Power Query queries to individual views of SharePoint lists. Any ideas on that?

On the other hand: is there a way to connect Power Query to a SharePoint API and directly request individual columns of a list?

Looking forward to your input (even some exhaustive documentation)!

Kind regards

Petter 

[Daryl-Lynch-Bzy]

Hi @PetterR  - sorry I did a bit more research.  This is an issue for PowerApps as well.  Could you switch from using SharePoint to using a Dataverse table?

[PetterR]

Hi @Daryl-Lynch-Bzy , the data we want to connect to is maintained in SharePoint; I'm not sure if this information is also mirrored in Dataverse table, if so, I can definitely connect to that. However, changing data entry from SharePoint to Dataverse would be a process change on the business side.

[Daryl-Lynch-Bzy]

Hi @PetterR - the limitation you are referring to only applies to V2.0 connector for SharePoint (Power Query SharePoint Online list connector - Power Query | Microsoft Learn). 

The limitation does not apply to v1.0 - Power Query - SharePoint join limit

Could you re-try building the:

SharePoint.Tables( siteurl , [Implementation=null, ApiVersion=15] )

 Sorry, but the performance may become an issue with this approach depending on the length of the List.

[PetterR]

Hi @Daryl-Lynch-Bzy , applied the v1.0 of SharePoint.Tables as follows: 

Execution gives the lists in the SharePoint site and indicates one error:

Stepping into the relevant list gives this error (in German only):

Error message indicates "Data source error: SharePoint: Bad request: remote server returned an error: (500) internal server error. (The query cannot be closed, because the number of included lookup columns exceeds the threshold for lookup columns.)..."

Did i mix something up with the options of SharePoint.Tables? Or do I have to configure sth in the data source? Or did I miss a step out when refreshing the query (like a "re-compile" or sth like that)?