cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Getting Excel file content from different SharePoint sites

Hi,

 

I have several SharePoint sites that have an identically named folder which contains a Excel file. I would need to go through a list of site URL's, get the files and append these files into one table.

 

I have a separate SP list with all site URL's which I filter to get the list of URLs I want.

I also made a custom function which gets the Excel file from the correct folder given an URL as a parameter:

(URL as text) as table =>
let
    Source = SharePoint.Files(URL, [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = URL&"/powerbi/")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Hidden Files1",{"Extension", "Date accessed", "Date modified", "Date created", "Attributes"})
in
    #"Removed Columns"

 

 

I am able to get the content as binary into my table by invoking the function but when I try to 'Combine Files' it gives me an error on Formula.Firewall.

UrlList.PNGUrl list

 

 

 

 

 

SiteContent.PNGInvoked function to get contentError.PNGError on combining

 

 

 

 

 

 

 

 

I went through a couple of threads with similar issues and read through Ken Puls' blog, but I can't get my head around on how to fix the issue. 

Any help on this issue?

Thanks in advance!

 

Br,

T

 

 

1 REPLY 1
Anonymous
Not applicable

Re: Getting Excel file content from different SharePoint sites

I enabled 'Ignore Privacy Levels' in the current file and the error disappeared. However, is there a solution to the error other than ignoring privacy levels.

 

Br,