Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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:

 

7.jpg

 

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

 

1.JPG

 

2.JPG

 

I then selected all of the applicable document libraries.

 

3.jpg

 

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. 

 

4.jpg

 

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. 

 

5.JPG

 

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.

 

6.jpg

 

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!

1 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:

 

MFelix_0-1619099442025.png

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

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):

 

MFelix_0-1619082747938.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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 arelot of columns that can be expanded using this connector type.

 

Thank you!

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:

 

MFelix_0-1619099442025.png

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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!!

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.