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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
scottkruyswyk
Frequent Visitor

"User was not authorized" using RelativePath option of Web.Contents() for excel files on Sharepoint

I am building a Power BI report that needs to be published to web with automatic refreshes. The report I am building pulls data from 10 excel documents, all located in separate document libraries on sharepoint online. Each excel document has 10 tabs, each with 2 named ranges that I need to pull data from - so that would result in about (10 documents) * (10 tabs) * (2 ranges) = 200 queries to pull all data in manually. Clearly a no-no.

 

I created a couple functions that would allow me to use a dynamic query to get all this data with only a few queries - the code for one of those functions is below:

 

(Location as text, Period as number) as table =>
let
	location_trimmed = Text.Remove(Location, {".", " "}),
	Source = SharePoint.Files("https://xxxxxx.sharepoint.com/teams/teamOne/" & location_trimmed, [ApiVersion = 15]),
	#"File Name" = location_trimmed & "MillScorecard.xlsx",
	#"MillScorecard" = Source{[Name=#"File Name",#"Folder Path"="https://xxxxxx.sharepoint.com/teams/teamOne/" & location_trimmed & "/Shared Documents/"]}[Content],
	#"Imported Excel" = Excel.Workbook(#"MillScorecard"),
	#"Range Name" = "Period " & Number.ToText(Period) & "!P" & Number.ToText(Period) & "_All_data",
	All_data_DefinedName = #"Imported Excel"{[Item=("Period " & Number.ToText(Period) & "!P" & Number.ToText(Period) & "_All_data") ,Kind="DefinedName"]}[Data]
in
	All_data_DefinedName

 

However, I soon found out that Power BI online doesn't support automatic refreshes for dynamic queries:

 

https://community.powerbi.com/t5/Service/Problem-with-automatic-refresh-data-source-not-supporting/t...

 

I found an article that used Web.Contents options as a hack get around this, so I figured I would give it a shot - using the RelativePath option, it seemed like I could get the same kind of hack done. 

 

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

 

My resulting code:

 

(Site as text, Location as text, Period as number) as table =>
let
    location_trimmed = Text.Remove(Location, {".", " "}),
    relative_url = Site & "/Shared Documents/" & location_trimmed & "MillScorecard.xlsx",
    url = "https://xxxxxx.sharepoint.com/teams/teamOne",
    Source = Excel.Workbook(Web.Contents(url, [RelativePath=relative_url]), null, true),
    #"Period 1!P1_All_data_DefinedName" = Source{[Item="Period 1!P1_All_data",Kind="DefinedName"]}[Data]
in
    #"Period 1!P1_All_data_DefinedName"

 

When I tried to invoke this function - after entering my SharePoint credentials - I get this error:

error.png

 

When I change the function to use the entire url as the base url in the Web.Contents() call, it works perfectly fine.

 

Is there any way I can get around this? Does anybody know of a better way to go about doing this??

 

1 ACCEPTED SOLUTION

Hi @scottkruyswyk,

 

I found a similar thread here which mentioned a workaround that change the URL by replacing https with http. Could you go and check if it helps?Smiley Happy

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Employee
Employee

Hi @scottkruyswyk,

 

Based on my research, there is a limitation to use base path and RelativePath within the Web.Contents, that the base path by itself must be accessible, or the connection will fail.

 

So you may need to contact your SharePoint Administrator to make sure you have access to both the full path and base path to make the connection successful in this case.

 

Regards

@v-ljerr-msft Thanks for your response. The account I was using to sign in has full access to the base path and all of the sub-sites/document libraries that I am trying to access.

Hi @scottkruyswyk,

 

I found a similar thread here which mentioned a workaround that change the URL by replacing https with http. Could you go and check if it helps?Smiley Happy

 

Regards

@v-ljerr-msft It worked! Thanks so much.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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