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.

[v-jayw-msft]

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.

[primolee]

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!

[aTChris]

@primolee 

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.

[primolee]

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
    Source

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

[aTChris]

@primolee 

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"

[primolee]

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

[aTChris · Accepted solution]

@primolee 

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.

Download here 

[Anonymous]

Hi @aTChris ,

Nice that you made it works, could you update the link to the pbix file.
The current download doesn't seems to work anymore, tnx in advance 🙂

[CWPRenewables]

Hi aTChris,

I have a very similar problem, and now reading more on this post, I realise why my solution hasn't worked.

I would like to look at your solution to try and understand it, as I don't quite understand what you mean by "set all URLs as parameters", but there the Download path is dead.

Any chance you could take a look at my post and comment?

https://community.powerbi.com/t5/Power-Query/SharePoint-Sites-Authentication-as-part-of-Web-Contents...

[prill2ta]

Hey @aTChris 

Are you able to reshare your file?

[Arendp]

Hello @aTChris 

Is the solution you suggested here, still available? I think this might help me with a problem I have.

I see it was not the solution for @primolee yet, but I believe it might help me.

It's referred to this post - ->

[aTChris]

@Arendp 

Hi, we found a couple of solutions that worked. I suggest you create another thread with details of your issue and I will post whatever I have that might help.

Mention me in it

Thanks

Chris

[Arendp]

@aTChris 

Thanks for your answer.

I had already started a new topic here with my question.

Would be nice if you can have a look, I think it has to do with the same as this topic.

Arend

[primolee]

Hello aTChris,

Thank you first of all for taking the time to solve my problem. 🙂

Just checked what you did.  You have made fileName as parameter.  In the sample there are 2 media sources, so you created 2 new parameters for each, this will make the value of parameter in double quotes, and that is why it works.  In other words, file names are static.

However, in reality, sometimes there can be more than 10 or even 20 different vendors.  With your method I will need to create more parameters.

With my original code in Consolidated Media Raw Data, you will see there is a column called "Folder Path" at the step of Invoke Custom Function.  When invoking processors, I will pass folder path + file name (_[Folder Path] & _[Name]) to processors.  This way, I do not need to create that many parameters.

Record.Field(processorRecord, _[Custom Function])(_[Folder Path] & _[Name], _[Publisher])

However, if I do this, it will not work in BI Service...

Moreover, sample is a simpler version of what I am doing.  Besides media vendors, client and media campaign are both dynamic as well (that's why I have ClientFilter and CampaignFilter as parameters as well).  If there are 4 clients, 4 vendors and 4 campaigns, I will need to create 4 * 4 * 4 = 64 new parameters because their paths will be all different...

Therefore, I want to make it as dynamically as possible, that's why I am passing file name with relative path dynamically to processors instead of hard-code it.

I am so sorry to tell you that your method would not work for me...  I really wish I could use static URL but I cannot due to too many sources...

Any other thoughts? >_<

[aTChris]

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

[primolee]

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!

[aTChris]

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

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

[aTChris]

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

[primolee]

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