Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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:
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.
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:
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??
Solved! Go to 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?
Regards
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?
Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
73 | |
66 | |
60 |