iterate through sharepoint

[Yggdrasill]

Hi, 

I've struggled with this one for far too long now so before I do anything more I'd like to see if someone here can assist.

I have list of project numbers which I can query either from Sharepoint and that I don't have any issues with.

The Project number are used to query the content within a sharepoint folder in this format: https://{company}.sharepoint.com/sites/{project-number}/

I can easily do this for one folder using the in-built sharepoint connector and I created a function which is used to iterate throught the links.

Here's the code:

let
  
// Get list of Project numbers

  Source = CommonDataService.Database("COMPANY.crm4.dynamics.com", [CreateNavigationProperties = null]),
  Navigation = Source{[Schema = "dbo", Item = "oc_project"]}[Data],
  
//Create Sharepoint URL

  #"Added custom" = Table.TransformColumnTypes(Table.AddColumn(#"Navigation", "Path", each "https://COMPANY.sharepoint.com/sites/" & [PROJECT_NUMBER] & "/"), {{"Path", type text}}),

  //Iterate through Sharepoint URL to get content metadata

  #"Invoked Custom Function" = Table.AddColumn(#"Added custom", "Output", each Output([Path])),
  #"Expanded Output" = Table.ExpandTableColumn(#"Invoked Custom Function", "Output", {"Name", "Extension", "Date modified", "Date created", "Size", "Content Type", "Kind", "Folder Path", "Hyperlink"}, {"Name", "Extension", "Date modified", "Date created", "Size", "Content Type", "Kind", "Folder Path", "Hyperlink"}),
  in
    #"Expanded Output"

Function code looks like this:

let
Source =  (PROJECT_NUMBER as text) as table =>
let
  Source = SharePoint.Files(PROJECT_NUMBER, [ApiVersion = 15]),

//Get Metadata
  #"Expanded Attributes" = Table.ExpandRecordColumn(Source, "Attributes", {"Size", "Content Type", "Kind"}, {"Size", "Content Type", "Kind"}),
in
    #"Expanded Attributes"
    in Source

As you can see I'm using the Sharepoint.Files() function and this method works perfectly in Power BI Desktop as I can iterate through Project numbers and get metadata of the content of the sharepoint files!

This method does not work when trying to refresh in Power BI Portal

I've seen this error on many occasions and I've fixed it using Web.Contents() and RelativePath but I can't use the Sharepoint API to get the same content as with Sharepoint.Files() 

Is there a way for me to get Sharepoint file Metadata by using Web.Contents() ?

[v-xiaosun-msft]

Hi @Yggdrasill ,

I found that there are some similar situations to you. Dynamic URL with Web.Contents and RelativePath worked well.

I think that you can reference to the following documents which may be helpful to you.

Solved: DataFlow does not save due to dynamic data source - Microsoft Power BI Community

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Chris Webb's BI Blog: Handling Multiple URL Query Parameters With The Same Name Using Web.Contents I...

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Yggdrasill]

Thanks for the reply. I'm well aware of web.contents() and RelativePath and how to use it in order to scheduled refresh in the cloud. That's not my issue.

My issue is the Sharepoint API vs Sharepoint.Files() and how to get the same content from the API as we get from the in-built Sharepoint.Files() function in Power Query