Retrieve All Rows and Columns

[Singh_min2]

Hello, 

I am trying to create a Power BI online report by connecting to a SharePoint Online list/ SharePoint Folder/ Sharepoint List to retrieve all the files and metadata dynamically. However, I'm encountering issues when trying to view all rows and metadata in Power BI.

What I Need:

1. Retrieve All Rows and Columns: I need to pull all rows and columns from the SharePoint list or document library without manually specifying column names as there is metdata associated with each file in columns (e.g. file names, paths, creation date, modification date, author, editor, etc) 
2. View Folder Structure and File Metadata: I would like to dynamically load the folder structure and associated file metadata (e.g., file name, folder path, modified date, created date, file size, etc.).
3. Handle Large Lists: My SharePoint library or list have more than 100000 items and I need a way to handle this large dataset in Power BI.

Any help or suggestions on how to correctly connect to my SharePoint list and retrieve all the data I need would be greatly appreciated!

Thanks in advance.

[christinepayton · Accepted solution]

The OData connector is the one that will have both the file path and the metadata. SP folder connector has path and file content but not custom columns, SP list connector can get doc library metadata but doesn't have path. OData has both path and the columns. 

To use it, you connect with the OData connector type append the OData URL onto the end of your site address, then grab your library.

The path looks like this: https://<tenant>.sharepoint.com/sites/<sitename>/_vti_bin/listdata.svc

If you are working with a large size library, remove any columns you're not using and make sure not to do a bunch of non-foldable query steps - that's your best chance for decent speed. I've used the OData connector on larger libraries before without issue so you should be ok as long as you don't try to tack on any expensive query steps. 

I did a video on the OData connector here if you want more info: https://youtu.be/yeIQ9jKln_I

[Singh_min2]

Hi Christine, another query please, will this work as live reports? or I need to do anything else to make it live report?

Appriciate your help. Thank you

[christinepayton]

What do you mean by live report? You can schedule refresh on it but not direct query.

[Singh_min2]

Great help! thank you Christine.

[Anonymous · Accepted solution]

Hi, @Singh_min2 

We are delighted to hear that the issue has been resolved. If you feel inclined, please consider marking  christinepayton's solution as such, this will assist other community members facing the same challenge in finding a resolution more swiftly.

Should you require any further assistance, please do not hesitate to get in touch.

I look forward to your response.

Thank you in advance
 

Best Regards,

Leroy Lu

[christinepayton · Accepted solution]

The OData connector is the one that will have both the file path and the metadata. SP folder connector has path and file content but not custom columns, SP list connector can get doc library metadata but doesn't have path. OData has both path and the columns. 

To use it, you connect with the OData connector type append the OData URL onto the end of your site address, then grab your library.

The path looks like this: https://<tenant>.sharepoint.com/sites/<sitename>/_vti_bin/listdata.svc

If you are working with a large size library, remove any columns you're not using and make sure not to do a bunch of non-foldable query steps - that's your best chance for decent speed. I've used the OData connector on larger libraries before without issue so you should be ok as long as you don't try to tack on any expensive query steps. 

I did a video on the OData connector here if you want more info: https://youtu.be/yeIQ9jKln_I