Lookup Column Threshold

[PetyrBaelish]

I am creating several OData connections to XML Forms Libraries stored on SharePoint Online.

This includes 1 unique libraries and 14 libraries which utilise the same content type - therefore the same columns.

For these 14 libraries, the column types I am retrieving are as follows:

Single line of text - 6 columns

Multiple lines of text - 3 columns

Integer - 18 columns

Yes/No - 16 columns

I successfully created the OData connection for my unique library and the first 4 libraries (Edit Queries -> New Source -> OData -> Rename the query -> after doing this for all 4, I selected Close & Apply). All were successfully loaded to the data model.

Then I created the source for the 5th library (containing 101 list items), but this failed and I got the below error message:

Error message:

The library that I tried had the most data in it (101) rows.

I then deleted my 5th query, created a new query this time point to one of the sources that only has 3 rows in it - this was successfully loaded to the data model. I then reattempted to create a 6th connection tot he library with 1010 items in it, and it failed.

FIrstly, I don't understand why I'm getting this error. I believe I am using 0 lookup columns.

Secondly, why does this threshold limit behave differently on a data source with 101 rows as it does with one with 3 rows. Surely since they have the same columns they should behave the same?

Thirdly, does the lookup column threshold apply across all sources? So if I have 9 sources each with 1 lookup each that's it? I'm using too many? I've tried reading up on this but couldn't find an answer relating to Power BI.

Most importantly, how can I get around this? Can I create 1 OData connection to all 14 libraries (if so how to I append all 14 sources into 1 query)?

[v-yuezhe-msft]

@PetyrBaelish,

Based on my research, the following column types are defined as lookup columns:

a.Standard lookup columns
b.Managed metadata columns
c.People and groups columns (These also include the Created by and Modified by fields, see below!)
d.Workflow Status columns
e.System generated fields
  Created by
  Modified by
  Name (linked to Document)
  Link (Edit to edit item)
  Name (linked to Document with edit menu)
  Type (icon linked to document)

Secondly, as per this blog, threshold limit applies on each query you import into Power BI Desktop, and sharepoint online shares the same threshold limit which is described in the blog.

Thirdly, split your query when importing data to Power BI Desktop as mentioned in this similar thread, then check if you can work around this issue.

Regards,
Lydia

[PetyrBaelish]

Thanks for your response.

Based on your first answer - you have confirmed that I am not using any lookup columns (mine are single line of text, mulitple lines of text, integer and yes/no fields - all created by the user), so I am still confused why this problem is occurring.

On the second point, if the limit is 8 on each query, then once more that should be good news as it means my limit is 8 x 14 (112) so even if I had a column that is somehow being classed as a lookup I would only have 14 (1 per query) - once more I'm nowhere near the limits.

Regarding your 3rd point - I have just tried this:

for my 13 libraries that are working I have one query per library that retreives all 43 columns.

For my 14th library - the only one that doesn't work I have created two queries - each with 22  columns (the ID column being on both). Each query loaded successfully. Within Power Query Editor, whilst ont he 2nd query I selected merge queries, selected the two appropriate queries, selected full outer join, but the results are once more telling me I am exceeding the number of lookup columns (sigh).

As mentioned in my first response I find it very strange that this problem is only occurring on the data source with 101 rows being returned and not on the other ones (where there is a maximum of 44 rows being returned). They're exactly the same columns (and data types) on all 14 data sources - surely the problem of the lookup column threshold shouldn't be impacted by the rows returned and should either be occurring on all 14 libraries or none - not affecting 1 but absolutely fine on the other 13. This is a concern as the amount of data will grow over time.

Based on the reports I need to create, I need the data from all 14 libraries, with the exact same 43 columns, to be part of the same dataset. I was expecting it to be a simple create sources and append them all.

[PetyrBaelish]

Further to my post - this problem is only occurring on 1 of the Odata connections (the largest one with 101 rows) - the other 13 all load fine.

To me this suggets a timeout issue - but we're only talking 101 rows here - how can that be activating a timeout, it's such a small number.