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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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