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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Microsoft Employee
Microsoft 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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