Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
PetterR
Frequent Visitor

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 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 

4 REPLIES 4
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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?

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
Resident Rockstar
Resident Rockstar

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.

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

image.png

 

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

image.png

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

image.png

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)?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors