The query cannot be completed because the number of lookup columns it contains exceeds

[veena]

Hi,

Unable to connect to the SharePoint list from PowerBi Desktop.

The issue is “Number of lookup columns in the list exceeds threshold”. 

The sharepoint list contains 77 custom colums including 16 person or Group columns ( which SharePoint considers as lookup column). I need to show only a few columns in PowerBi .I am connecting to sharepoint List using Get Data-->More-->Other-->SharePoint List. Is there any way to get data from my list?

Error

 Thank You

[Anonymous]

Is this an on premise sharepoint?  If so, i'd recommend going directly to the SQL database and getting what you need from the AllUserData table.  You'll need to map out what the List ID is and what data type columns you'll need, but you won't have the same limitations.

[akbaree]

Hi

I am facing this same issue but my list is on SharePoint Online. Any suggestions to resolve this issue when using SharePoint Online will be very helpful

Thanks

[veena]

Hi Ross,

Thanks for your reply. The sharepoint on premise is used.

Can you please elaborate the steps if you dont mind.

Thanks.

[Anonymous · Accepted solution]

First you'll need access to the SQL database that your on-premise sharepoint runs on.  You might need to talk to your SharePoint DB admins

Next you'll need to locate which database contains the list you care about.  Your SharePoint Administrators can assist there if you have more than one.

Next you'll need to find the AllUserData table, which contains the records of all of the lists in that database.  Each list as you see them in sharepoint has a unique ID under TP_ListID column.  You can get this list from your SharePoint Administrator or by doing a search for unique values in their respective columns.

Lastly you'll see there are columns that are listed like Nvarchar1, Nvarchar2 etc.  These are the data types that are possible in a given list and you'll need to identify which columns have been used in the list you care about.  There will be a column that contains an XML summary of the data, which should point these out to you.  Using SQL server management tools is the best option here as it will cut down your searching time.

Once you have the appropriate list and columns, you can write an SQL statement to produce the Table you want in Power BI.  From here you can use which columns you want and only have the query bring in those columns.