Loading all columns issue from the Sharepoint List

[Iryna-BI]

Hi everyone,
I’m working with a SharePoint List as a data source in Power BI. The list contains around 30 lookup columns, and I need to perform multiple transformations in Power Query (unpivoting, adding custom columns, merging related tables, etc.).

Here’s the issue I’m facing:

• When using [Implementation="2.0", ViewMode="Default"], Power BI loads data successfully - but only a subset of columns (not all lookup fields). Selecting ViewMode="All" turns an error.

• When switching to ApiVersion=14, Power BI detects all columns, but then I can’t load the data at all - it fails during the import process, even before I can apply transformations.

I’ve tried adjusting query folding, disabling load for some tables, and checking permissions — but the behavior remains the same.

Has anyone found a stable configuration or workaround that allows:

• loading all columns (including lookup fields), and

• maintaining a reliable refresh and transformation process in Power Query?

Any guidance or working setup examples would be greatly appreciated.

Thanks 

[Poojara_D12 · Accepted solution]

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.

[Iryna-BI]

Thank you @Poojara_D12  for your response. 
I really need to load all the lookup colums, because all of them imprortant for business. 

I will try lo export it to Dataflow first. 

Thank you.

Best regards,
Iryna 

[rubayatyasmin]

Hi @Iryna-BI 

I think you are facing a limitation. SP List v2.0 works differently from v1.0, as such it has 12 lookup column limitation. 
Refer: Power Query SharePoint Online list connector - Power Query | Microsoft Learn 

If it's not the case, then use default mode, make sure your site has all the columns visible. 
refer: Power Query SharePoint Online list connector - Power Query | Microsoft Learn

[v-sshirivolu]

Hi @Iryna-BI ,

I would also take a moment to thank @rubayatyasmin  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[v-sshirivolu]

Hi @Iryna-BI ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you