Forum Discussion
Lookup Column Threshold
Anonymous,
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
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.