Forum Discussion
Web.Contents with dynamic file URL
I've got it working. All ive done is set all URL's as parameters, I also added a space between parameters and the & in the functions. All minor stuff so this makes me think there is more to it.
Anyway I uploaded it to my service updated the facebook file and refreshed and the clicks updated.
I've updated the pbix with your sharepoint path, here are the files again.
You should not have an issue linking to a sharepoint file in the cloud. What error do you get?
This is an example of what works for me.
let
Source = Excel.Workbook(Web.Contents("https://xxxx.sharepoint.com/sites/powerbi/Shared%20Documents/xxxx.xlsx"), null, true)
in
#"Source"
With regards to your issue using a relative path. The issue is because the parth you've set is not correct, if you try to navigate to that location in a browser you will see it redirects to /Forms/AllItems.aspx, Ive not found a solution to define the root location and relative path for sharepoint docs.
Do you know the quick method in excel to get the path of a file in sharepoint?
File -> Info -> Copy Path
Remove "?web=1" and you have an encoded path to your file.
Hope that helps.
- Hello aTChris,
Thank you so much for your reply. Yes your codes will work as the URL is fixed in double quotes.
Because there are more than 1 excel file in different folders that I want to process, I am passing the full URL as a variable “fileName” to this function I created.
Web.Contents with fixed URL in double quotes will work in BI Service, but will not work if I use a variable as URL. This is why I use relativePath.
In the error message shown in the last image, URL shown in the very first line is the correct path with correct file name and extension (mouse-over will show the full URL). Therefore, I think the URL is not wrong like you said.
Original codes with Web.Contents(fileName), it says something about unable to process file type and unsupported function: Web.Contents. Sorry that I don’t have the computer with me right now and I forgot what exactly the error is.
Any other idea?
