connect to sharepoint folder or onedrive folder with subfolders

[hgorina]

I am working with the data folder on a local drive.  The folder has one level of subfolders, and I am analyzing the files in these subfolders. Here is  how the query start looks.

I have published my reports and now need to configure automatic refresh at least once a day.  My first thought was that I can set robocopy to either onedrive or sharepoint folder, and then connect to that data in the cloud.  

The problem is I cannot figure out how to connect to the whole folder using either onedrive or sharepoint folder. I will need to read data from all files in the subfolder of the folder including matadata loke attributes (file size, date accessed, date modified and such.

Any suggestions?

Is my only option a gateway?? 

many thanks for all your help!

[v-qiuyu-msft]

Hi @hgorina,

To get all files in SharePoint folder, you can use SharePoint Folder data source type, and type root URL. Then you can get all files under this folder and it's related information. See:

Once you publish the report to Power BI, as the dataset is connecting to SharePoint site, the gateway is not required. You can configure the schedule refresh as daily frequency.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[hgorina]

Many thanks for your reply, Qiuyun Yu

I was able to connect to the folder on SharePoint in a roundabout way. Pointing directly to the folder did not work. here 

1.  connected to the entire site   = SharePoint.Files("https://perkinswillinc.sharepoint.com/", [ApiVersion = 15]) 
2.  filtered rows to only look in my folder = = Table.SelectRows(Source, each Text.Contains([Folder Path], "https://perkinswillinc.sharepoint.com/Shared Documents/DigitalPractice/BIMLISt_Reports/"))

the problem now is that I am getting error processing the files, I am using a function which gives me an error

the problem now is that I am getting error processing the files, I am using a function which gives me an error

here is a function

 = (path as text) =>
let
Source = Xml.Tables(File.Contents(path)),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"ID", type text}, {"SchemaVersion", Int64.Type}, {"Name", type text}, {"LastUpdatedDate", type datetime}, {"IsRemote", type logical}, {"RemoteServerAddress", type text}, {"IsCloud", type logical}})
in
#"Changed Type"

When I try to invoke this function on https://perkinswillinc.sharepoint.com/Shared Documents/DigitalPractice/BIMLISt_Reports/4298b4aa-3dcb-4c34-afaa-4d556bb2b3a9/bimlist.bldb as a path, i get an error

apparently it does not take the sharepoint path 😞

any insight will be much appreciated

thanks again for your help

[v-qiuyu-msft]

Hi @hgorina,

The error message indicates that the path is not the absolute URL, please check if you can use this URL to find the object in SharePoint Online: https://perkinswillinc.sharepoint.com/Shared Documents/DigitalPractice/BIMLISt_Reports/4298b4aa-3dcb-4c34-afaa-4d556bb2b3a9/bimlist.bldb.

Best REgards,
Qiuyun Yu