Cannot Connect to files within a folder inside a SharePoint Document Library

[Anonymous]

Hello Everyone and thanks in advance for any assistance possible. 

I am creating a report in PBI Desktop that has datasets that connect to multiple SharePoint document libraries using the SharePoint Online List source connector. The goal is to gather a list of all of my company's controlled documents to easily and quickly identify the owner of the document, the approval status, the last review date, etc. so that we can properly manage controlled documentation. 

Example of desired end state of table:

I selected SharePoint Online List (2.0 Beta) from New Source, then entered in the root URL for the SharePoint site.

I then selected all of the applicable document libraries.

The reason I use the 2.0 Beta version is because this is the only way I have been able to pull over Approval Status and some other custom columns added to the document libraries such as "Owner" and "Last Review Date". Using 1.0, I am unable to obtain these columns (or have not found out how to do so). 

If the document library contains only files, it works perfectly. I can get all of the details I need. 

However, if the document library contains a folder, there is no way for me to get in there and get the details of the files within that folder. The folder is listed, but the documents within it are not. 

I can pull those particular files by using the SharePoint Online List 1.0 or the SharePoint List sources, but again these do not contain the Approval Status (which I need). 

I have also tried the SharePoint Folder source, but for some reason this only pulls certain folders and not all folders within my root SharePoint site, and does not include all of the files within the folders and again does not include the approval status.

And of course, I cannot link directly to the subfolders as I receive the subfolder error.

Does anyone know how I can grab all of the files in the selected Document Libraries, including those within sub folders within these libraries, and also pull the Approval Status and custom/manually added columns such as the Owner and Last Review Date as shown above? I have searched other Q&A's and posts but have not found one specifically addressing this scenario. 

Thank you!

[MFelix]

Hi @Anonymous ,

Is your issue related with getting the files or getting the folder path? I have made some tests with version 1 of the online lists and was abble to get the additional fields and also the path, altough the path you need to pickl it up from another colum:

• Expand the file column and the field: ServerRelativeUrl

Has you can see below I have the custom fields and the path (highlighted column is a custom field):

[Anonymous]

Hi @MFelix,

It is specifically the approval status that is missing when using SharePoint Online Lists 1.0. I can pull all of the files, and do see Owner and Last Review Date, which I need, but I am not seeing any way to get the approval status. I need all of the fields shown in the "Example of desired end state of table" picture above. With SharePoint Online Lists 1.0, I cannot figure out how to pull the Approval Status. It does not seem to be available. Were you able to find Approval Status in your test? Maybe I am just missing it: there are a lot of columns that can be expanded using this connector type.

Thank you!

[MFelix · Accepted solution]

If you are talking about the status that you get when you do a check out and check in you can get that in the FieldValuesAShtml, FieldValuesAStEXT or FieldValuesForEdit and search for the field OData__ModerationStatus:

Not sure if this is the field you are looking for but if not please tell me.

[Anonymous]

That's it!! I had expanded almost every column to check for this and did not even think to expand 0Data_ModerationStatus 🙂 Thank you so much!!

[MFelix]

No problem. This information with different names and some of them are not really user friendly. 😄