Web.Contents with dynamic file URL

[primolee]

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.

[gauravsukhadia]

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. 

[aTChris]

@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.

[primolee]

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

[aTChris]

@primolee 

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

Best,

Chris

[primolee]

I thought I did by accepting one of your reply as solution. Is there another way of marking this thread as solved?

[primolee]

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!

[primolee]

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!

[aTChris]

@primolee 

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.

[primolee]

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?