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.
Progress though, is that error in the Service or Desktop?
In desktop you can change your security settings to resolve that. But in Service you cant. The solution ive used in the past is to include the Function in the query.
Facebook Function =
let
{{Function}}
in
#"Function",
Source = {{Query}}
in
#"Query"
I dont see a way around the Web.Contents issue. The Service needs to validate the URL and SharePoint does not support query paremters as far as I am aware, only full paths to files.
Hello ATChris,
The error occurs in Desktop, so I guess with your method I can get around it. Let me give it a try.
I guess there is really no way to pass it but to use your way due to Web.Contents service check issue.
Reason why I do not like extracting binary directly is that there will be too many functions and parameters created by BI, and it will make query list in the left panel very messy, especially there is 1 extraction per processor and I have so many processors...
I have made the whole process very automatic. Media vendors send emails with excel attachment, MS Flow will save attachments to a dedicated folder in Sharepoint site according to client, vendor and media campaign. The only thing users need to do is to open BI file and refresh, and viola~
What I am trying to achieve here is to go onto BI Service and set up schedule refresh, so that users don't even need to hit the refresh button at all.
Worse case is, we will ask users to refresh in Desktop and then publish to Service, and not to refresh in BI Service. 😛
Thank you again!!! 🙂
Hello aTChris,
Indeed, thank you so much for all the time you have spent. Hopefully one day BI Service can be as friendly as BI Desktop. 😛
Best regards,
David
- I thought I did by accepting one of your reply as solution. Is there another way of marking this thread as solved?
Hello aTChris,
Wahahahahahahaha!!!!! I got it working! I used Sharepoint.File with a different approach without Web.Contents and it works like a charm!
(fileName as text, filePath as text, publisherName as text) => let Source = SharePoint.Files(foldersPath, [ApiVersion = 15]), SourceBinary = Source{[Name=fileName,#"Folder Path"=filePath]}[Content], #"Imported Excel" = Excel.Workbook(SourceBinary),- First, use Sharepoint.Files to list all binary files
- With fileName and filePath parameters passed to this processor, get the dedicated binary I want
- Extract binary with Excel.Workbook
With this way, I don't need to use Web.Contents. Therefore I can use dynamic URL and there wouldn't be any problem in Power BI Service.
This will be the exact solution to my problem, maybe you can edit your solution reply with this if you want.
Thank you again for all the inspiration!
Check out this link Chris Webb nicely explained a way around this web. contents service check issue at exactly 29 minutes and 35 seconds. https://www.youtube.com/watch?v=a0FqNLI0VsQ&ab_channel=RADACAD
Cheers.