Loading all columns issue from the Sharepoint List

Hi Iryna-BI 

When connecting Power BI to a SharePoint List that contains numerous lookup columns, you’re essentially hitting the inherent limitations and inconsistencies in how Power BI’s SharePoint connector interprets the SharePoint REST API. By default, when you use [Implementation="2.0", ViewMode="Default"], Power BI queries the modern SharePoint API endpoint (_api/web/lists/getbytitle(...)) and retrieves only a simplified subset of fields — usually excluding complex or deeply nested lookup columns — to ensure better performance and query folding. Switching to ViewMode="All" instructs Power BI to fetch every available field, including lookup and person fields, but this often causes errors or timeouts because the connector must expand multiple nested records and relationships that can exceed SharePoint’s internal request limits. Meanwhile, using ApiVersion=14 (which calls the older SOAP-based or OData v2 API) exposes all columns, including lookups, but that API is less stable and prone to breaking during the load phase, especially with large lists or when lookup depth exceeds one level.

A more stable approach is to keep [Implementation="2.0"] with ViewMode="Default" and manually expand the lookup fields you actually need using Table.ExpandRecordColumn() in Power Query rather than trying to pull everything at once. Alternatively, you can create a SharePoint view that exposes only the essential lookup fields and point Power BI to that view. For high-performance scenarios, some teams also export the list data to a Dataflow, Azure SQL table, or Fabric Lakehouse, where lookup resolution can be done more reliably. In short, there’s no single flag that will fetch all lookup fields without risk — the practical workaround is to selectively expand only what you need or stage the data externally before bringing it into Power BI.