Forum Discussion

PetterR's avatar
PetterR
Icon for Helper I rankHelper I
3 years ago

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

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 peo...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
PetterR
Icon for Helper I rankHelper I
3 years ago

Hi Anonymous , 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)?