Forum Discussion
Web.Contents with dynamic file URL
I have searched through the community and found out about the limitation of Web.Contents on Power BI Service. Data source is an Excel file in Sharepoint site, so I did some change to my codes.
fileName is a full URL file, for example, https://globalappsportal.sharepoint.com/sites/mySiteName/Shared Documents/General/data.xlsx
Original which does not work in BI Service:
(fileName as text) =>
let
Source = Excel.Workbook(Web.Contents(fileName), null, true),
Of course, in Power BI Service, it shows: You can't schedule refresh for this dataset because the following data sources currently don't support refresh.
Therefore, the following is my modification:
(fileNameWithRelativePath = Shared Documents/General/data.xlsx)
(fileNameWithRelativePath as text) =>
let
Source = Excel.Workbook(Web.Contents("https://globalappsportal.sharepoint.com/sites/mySiteName"&fileNameWithRelativePath), null, true),
Following is my google drive with folder structure and the pbix file.
https://drive.google.com/drive/folders/1zo8gqyDfqUxKvFqtagW0YgZFd8HZUpGQ?usp=sharing
In Power Query, you can ignore all the parameters in User Inputs folder. Then modify the sharepoint site URL of the following 3 queries:
Consolidated Media Raw Data
FACEBOOKProcessor
YOUTUBEProcessor
As there are many different excel formats from all of our media vendors, I create 1 processor to alter 1 excel into our standard format. In this sample I leave 2 processors: Facebook and Youtube.
Consolidated Media Raw Data is the main query. It starts with Sharepoint.Files to list all files of a site. After some filtering I will get the files what I want to process. Then at "Invoke Custom Function", according to the folder name, file name with folder path will be passed to corresponding processor for format alteration.
This pbix will work in Desktop, but when loading to BI Service it won't due to Web.Contents used in those 2 processors.
Error says: Unable to refresh the model (id=9998200) because it references an unsupported data source
If I use the full URL in double quotes in Web.Contents, it will work in BI Service, but if I have a parameter in Web.Contents, above error will show...
This will work in BI Service:
Source = Excel.Workbook(Web.Contents("https://globalappsportal.sharepoint.com/sites/mySiteName/Shared Documents/General/abc.xlsx"), null, true),
This will NOT work in BI Service:
Source = Excel.Workbook(Web.Contents("https://globalappsportal.sharepoint.com/sites/mySiteName"&fileNameWithRelativePath), null, true),
As the folder path and file name will always be different so that I need to dynamically pass fileNameWithRelativePath over, is there a way to fix it?
Thank you very much for your time and help.
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.
29 Replies
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?
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.
