Web.Contents with dynamic file URL

primolee 

I have another direction for you. I believe the Web.Contents within the processors fails because the Service performs statis analysis. Because its a parmater and not a URL it fails.

web-contents-m-functions-and-dataset-refresh-errors-in-power-bi 

So my thought is to use the SharePoint.List source rather than Web.Contents. You can also pass the FolderPath from the Consolidated Media Raw Data to limit the inputs. You might be happy to just limit by the name. Add the transformation step to extract the table and you should have the same output.

Good luck.

primolee 

After I submitted it I thought that might be the problem. I think the best solution will be to use the Sharepoint Folder query option.

Sharepoint Folder 

This will allow you to ingest all files and comine them if they are the same format. That therefore is the challenge isnt it, getting them to be the same format. When you have created the connection I think you will be able to update the Transform Sample File with a complex transformation script to conditionally transform them so they can be appended.

Hope that helps.

Hello aTChris,

In Consolidate Media Raw Data query, I could have done so already with Sharepoint.Files and I could open binary by clicking the button in binary column header. And you are right, I did not use this method because all formats are different, that’s why I need to invoke other functions to resolve non-standard formats of excels.

Thank you once again for taking the time, greatly appreciated!

Hello aTChris,

I just did what you suggested (I think).  Extract binary directly from the table by pressing the button in header column.

1. Use Sharepoint.File and filter [folder path]&[name] using fileNameWithRelativePath

2. Delete all columns except Content column

3. Extract binary with the little button in Content column header which then creates other queries and parameter automatically

There are 1 problem.  First query is Consolidated Media Raw Data, it will invoke the processors I made, and then processors will call other queries which were generated by step 3 to extract Excel binary.  Because processors already refer to other query, therefore, when invoking processors in Consolidated Media Raw Data, the following error message will show.

Formula.Firewall: Query "FACEBOOKProcessor" refers to other query or step, so it cannot access the data source directly.

Sorry I am using another computer today and the environment is in Chinese, the above error message is my translation and is not the exact words in English version.

So...  Using Web.Contents with relative path seems to be the best solution.  Still bottle necked... >_<

primolee 

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!!! 🙂

primolee 

I know what you mean, with the evolution of PBI you will probably find you can refactor the solution soon.

Fingers crossed that works, hopefully we can close this one off. It's been a good challenge.

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

primolee 

Great news!! please can you mark this thread as solved. 

Best,

Chris

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),

1. First, use Sharepoint.Files to list all binary files
2. With fileName and filePath parameters passed to this processor, get the dedicated binary I want
3. 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.