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.
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
Source
Power 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.
What I suggest is you share a public sharepoint or dropbox link with the files in. Im certainly not hthe most skilled person on this forum. Although Im up for the challenge you dont want to exclude the rest of the community.
What are the data sources of the three other files?
Previously ive had issues calling functions in other queries. What I did was combine everything into one query, it may be a security issue which wont show on Desktop.
e.g. I have a query that iterates through the pages of an API that limits the repsonses to 1000 records. When I had the functions seperatley the Service would not refresh. Desktop would. Combining them sorted it.
let MemberPages = let Source = Json.Document(Web.Contents("https://xx.xxx.com/api/xxx/xxx?size=1000")), TotalPages1 = Source[TotalPages] in TotalPages1, GetMembers = (pPage as text) => let gBaseUrl = "https://https://xx.xxx.com/api/xxx/xxx", gMaxSize = 1000, vOptions = [Query=[size=Text.From(gMaxSize), page=pPage]], Source = Json.Document( Web.Contents ( gBaseUrl, vOptions ) ), Records = Source[Records], #"Converted to Table" = Table.FromList(Records, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", REMOVED!!!}) in #"Expanded Column1", Source = List.Generate(() => 1, each _ <= (MemberPages), each _ + 1), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Start"}}), #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Query", each GetMembers([Start])), #"Expanded Query" = Table.ExpandTableColumn(#"Invoked Custom Function", "Query", {REMOVED !!!), #"Removed Columns" = Table.RemoveColumns(#"Expanded Query",{"Start"}) in #"Removed Columns"Hello aTChris,
Thank you first of all for the reminder of making environment public, however, I just tried opening a new team but maximum allowance in my organization can only be public within the organization, I cannot make it open to public...
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.
Thank you so much!
Best regards,
David
- Hello aTChris,
Oh! No wonder why authentication wouldn’t pass because it is pointing to root directory.
Let me give it a try tomorrow, it would be something like the following:
(fileNameWithRelativePath as text) =>
let
Source = Excel.Workbook(Web.Contents("https://globalappsportal.sharepoint.com/sites/mySiteName/Shared Document/General/"&fileNameWithRelativePath]), null, true),
Hopefully it will work in Power BI Service! 🙂
Thanks again!