Forum Discussion

hgorina's avatar
hgorina
Frequent Visitor
10 years ago

connect to sharepoint folder or onedrive folder with subfolders

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!

Need Help

3 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity Support
    10 years ago

    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's avatar
      hgorina
      Frequent Visitor
      10 years ago

      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's avatar
        v-qiuyu-msft
        Icon for Community Support rankCommunity Support
        10 years ago

        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