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.
Hi primolee ,
1. According to the picture, it says you are already sign in, try File -> Options and Settings -> Data source settings to clear permissions.
2. Don't have the same Operating environment with you so not sure.
3. Surely the Excel file is supported on Power BI Service, you can use Get data -> Files -> Share Point - Team Sites to connect files. And for Power BI Desktop, please try copy the file link and remove "?web=1" at end of the link then use Web connector to connect.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Hello Jay,
Thank you very much for your reply. Because there are more than 1 excel file in different folders that I want to process, I need to dynamically pass URL as a variable to the function I created for processing.
Let me try clearing permissions tomorrow and see if it helps.
Thank you!I've got a solution. I think the issue using relative path is that Sharepoint authenticates with the root folder URL which is a 301 to /Forms/AllItems.xspx.
The below works for me. Use "&" not RelativePath=
let Source = Excel.Workbook(Web.Contents("https://xxxx.sharepoint.com/sites/powerbi/Shared%20Documents/" & FileName), null, true) in #"Source"Then set the filename as a parmeter in a blank query and name it FileName
let Source = "SalesLegacy.xlsx" in SourcePower BI constructs the URL then authenticates in this case would be my best guess.
Hello aTChris,
Just gave it a try, your method will work in Desktop but still do not work in Power BI Service.
Image 1: Error shown in Dataset refresh
Image 2: Error shown in Dataset setting
The 3 queries shown in image 2 do not use Web.Contents, but will generate fileName and pass to the function which uses Web.Contents.
I just did some tests. Web.Contents("http://xxx/"&fileName) will only work fine if fileName is set with double quotes. However, when I pass fileName which is not in double quotes from another function. it will not work.
We have too many data source with different formats which we cannot standardize, I have to invoke corresponding functions to alter the excel into a standard format. Therefore, there are several excels in different folders for each provider, and value in parameter fileName will always be different due to different file paths. The way you mentioned with a new blank query fileName is still set with double-quote, it will not work for me because I have to dynamically change the value of fileName.
It is too hard to explain with words, hopefully you can understand what I am saying...
With Sharepoint.Files listing all files:
If excel in folder1, invoke function A, at the same time pass file name with path to function A.
If excel in folder2, invoke function B, at the same time pass file name with path to function B.
With this fileName funtion, it will not work in Power BI Service.
(fileNameWithPath as text) => let Source = fileNameWithPath in SourceI have prepared a sample .pbix file and a sharepoint site with the data sources. Please let me know if you want to give it a try. If you do, please PM me your email address so that I can add you to the site.
Thank you so much for your time and help.
PS. just a quick background, I am in a media agency. There are too many local media vendors who do not want to provide data with standard format excel, this is why I use Power Query for altering all vendors' excels into standard format.
