Unable to Connect to Data source

Question

Unable to Connect to Data source

Shaik, Rafath Jahan 25

We have recently added three new lookup columns to the existing document library which is connected to power BI dashboard.

After the new additions, Dashboard is not getting auto-refreshed and unable to connect the data source. Below is error message

Error: A DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold).
Is there any way to resolve this issue without deletion of columns and the Dashboard can get the data .

Any option we can exclude these columns in the dashboard while connecting the data source , Or any other workaround also helps .

Please assist us.

0 comments

1 answer

Your answer

Answer 1

Ana Le 2,345 Independent Advisor

Hi @Shaik, Rafath Jahan,

Based on the error message, this is a known limitation on the SharePoint side rather than Power BI. SharePoint has a lookup column threshold, and when the number of lookup-type columns (including Person, Managed Metadata, etc.) exceeds that limit, queries can fail with a 500 error like the one you’re seeing.

Because of that, once the new columns were added, Power BI may no longer be able to retrieve data from the library.

From what I’ve seen, there isn’t really a way to bypass this limit directly or exclude those columns at query time from the SharePoint backend. Some possible workarounds you might try:

Reduce the number of lookup-type columns (if possible)
Split the data into multiple lists/libraries
Create a view with fewer lookup columns and connect Power BI to that view instead
Flatten the data before bringing it into Power BI

Also, I happened to come across this article while troubleshooting a similar issue, it’s not an official Microsoft source, but it explains the limitation and some practical workarounds pretty clearly: How to avoid Lookup Column Threshold limit on a view | SharePoint Maven (just sharing as a reference from another user’s perspective.)

You can also refer to the official documentation for more details on SharePoint limitations: The number of items in this list exceeds the list view threshold - SharePoint | Microsoft Learn

If none of the above helps, you might want to consider opening a support ticket so Microsoft support can take a closer look at your specific setup.

Hope this gives you a bit more direction!

Shaik, Rafath Jahan 25 Reputation points

2026-04-20T08:37:50.49+00:00

Hi @Ana Le

Is this possible: Create a view with fewer lookup columns and connect Power BI to that view instead
Ana Le 2,345 Reputation points Independent Advisor

2026-04-20T08:50:13.05+00:00
Hi @Shaik, Rafath Jahan,

Yes, this approach can work, and it’s actually one of the common workarounds for this scenario.

You can create a view in SharePoint that includes only a limited number of lookup columns (under the threshold) and then connect Microsoft Power BI to that specific view instead of the full library.

However, there are a couple of important points to be aware of:

The total number of lookup columns included in the query still needs to stay under the threshold (typically around 12)

Even if columns are hidden in the view, if they are still being referenced in the query behind the scenes, the error may persist

In most cases, this works best when using the view URL or ensuring Power BI is only pulling the fields exposed in that view

So yes, it’s definitely worth trying, and many users have been able to restore refresh functionality this way.

If it still doesn’t work after limiting the columns in the view, then the only remaining options would be restructuring the data or reducing lookup usage overall. The only thing to keep in mind is that this is more of a workaround for the threshold limitation, not a permanent fix. If the underlying structure still has too many lookup columns, the issue could come back depending on how the query is executed.

Let me know how it goes!
Ana Le 2,345 Reputation points Independent Advisor

2026-04-30T06:57:40.05+00:00

Hi @Shaik, Rafath Jahan, just checking in that were you able to try connecting Microsoft Power BI to a limited-column view in SharePoint, and did it help with the refresh issue?