SharePoint Lists PBI/Power Query error, implementation 2.0, "RenderListAsDataStream".

Darren164 
This is happening because you are exceeding rows limits from 5,000 to 10,000, the lookup activity selected multiple columns and it brokes the sharepoint list limitation.

If you change the API default to specific API=15 in power query editor.  It will surely resolve the issue.Please follow below steps one by one.

• The root cause is a SharePoint server timeout. Switching your list column to multi-select added complex data processing. Combined with 10,000 rows and lookup columns, it broke SharePoint's internal limit (5,000 items) and caused the 500 server error.

• Fix it fast in Power Query. Open the Advanced Editor in Power BI and update your source step to use ApiVersion=15 instead of default. This bypasses the broken RenderListDataAsStream function while keeping your existing setup intact.

• Create a lightweight SharePoint view. Go to your SharePoint list in the browser and create a new view that removes non-essential multi-select and lookup columns. Then point Power BI to load that specific view.

• Add column indexes in SharePoint. In SharePoint List Settings, go to Indexed Columns and add indexes for your 2 lookup columns, the multi-select column, and ID. This stops SharePoint from timing out when reading the rows.

• Move the query to a Dataflow for a permanent fix. Set up a Power BI Dataflow in the cloud to pull the SharePoint list, then connect your desktop report to that Dataflow. Cloud servers handle large SharePoint lists much better and prevent future refresh errors.
  
  If this helps, please kudos and accept this as solution, help others !

Darren164 Before you spend an afternoon indexing columns, I'd push back on the row-count explanation, because two things in your own post argue against it.

You've had around 10,000 entries in that list for a while and it connected fine. And right now, today, Excel reads the same list, the same default view, at implementation 2.0, with no issues. A 5,000-item view threshold doesn't care which client is asking. It's evaluated server side, on the query. If the row count were tipping this over, Excel would be getting the 500 too. It isn't. So the row count is the environment, not the cause.

What that pair of facts actually tells you is more useful: the two clients are not asking SharePoint for the same thing. Same list, same view, different connector build, different request, and one of those requests is the one SharePoint can't serve. That reframes the problem from "my list is too big" to "something about the field set the PBI connector is requesting", which is a much smaller thing to chase.

Your own ApiVersion=15 result points the same way and I don't think you gave it enough credit. You wrote it off as "loads, but not default view, issues with lookup columns and multi choice fields". That's not a failed workaround, that's the answer narrowing itself. It loads when the field set changes. The problem is travelling with the columns, not the rows.

On which limit: the one that fits a list with lookup columns and a pile of multi-select fields is the column-type one rather than the item one. Microsoft's list view threshold guidance puts it as "Displaying 12 or more columns of the following types can cause a list view threshold error: people, lookup, and managed metadata." I'd be straight with you though. A multi-select Choice column, which is the thing you actually changed, is not on that list. So I'd treat that as the limit to test rather than as your diagnosis. It fits the shape of your list; it doesn't obviously fit your trigger.

The test is free and it's about ten minutes. In SharePoint, build a brand new view containing only the handful of columns you genuinely need, with no lookup, no person and no multi-select columns in it at all, and point implementation 2.0 at that view specifically. If it loads, you've confirmed the field set is the cause, and then you add columns back a few at a time until it breaks. That tells you whether it's a count you're crossing or one specific column doing it. If it still 500s on a stripped view, the field set is exonerated and I'd stop looking there entirely. Either way you learn something, which is more than the current situation offers.

The bit I genuinely can't explain from here is that reverting the multi-select back to single select didn't fix it. If that revert really did restore the previous state, then that column was the straw rather than the cause, and something else moved at the same time. Was anything else changed on that list around then? A new column, a changed default view, a retention or sync setting, anything by someone else on the team?

Hi Darren164,

I would probably push back a bit on the row-count explanation.

Microsoft documents two different SharePoint limits that often get mixed together:

• the general List View Threshold for large lists, which is around 5,000 items
• the lookup threshold, where showing 12 or more columns of type people, lookup, or managed metadata can trigger threshold errors

Because your list was working before and the breaking change was around field structure rather than row count, this feels more like a column/query-shape issue than a pure “too many rows” issue.

For the SharePoint connector, Microsoft does support both:

• Implementation = "2.0"
• ApiVersion = 14, 15, or Auto

but I would not assume that moving to API 15 by itself fixes this.

What I would try next is:

1. Create a new very small SharePoint view with only a handful of columns, ideally ID plus the core non-problem fields.
2. Make that the default view temporarily.
3. Connect with Implementation 2.0 using the default view.
4. If that loads, add the lookup / people / managed metadata / multi-value columns back a few at a time until the failing field or combination shows up.
5. Once identified, keep that field out of the direct SharePoint pull if possible, or split the logic so the heavy/complex columns are handled separately.

The reason I’d test it this way is that Microsoft’s Implementation 2.0 connector supports a Default view mode, so reducing the columns returned by SharePoint is a legitimate troubleshooting step, not just a workaround.

If the minimal default view still fails in Power BI Desktop but works in Excel with the same site and connector version, then I’d start suspecting a product regression and open a Microsoft support ticket with the correlation ID / request ID from the error, because at that point the difference is no longer just list design.

Thank you so much! I kid you not, the solution was to remove the 'count' function set on a column in the default view. Apparently someone in my team added that when viewing the List, and that was the straw. With that count function removed, I can connect to 2.0 default view with no issues. 
Absolutely wild that something so small could cause so much drama.