Forum Discussion

primolee's avatar
primolee
Helper V
6 years ago
Solved

Web.Contents with dynamic file URL

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.   f...
Need Help
  • aTChris's avatar
    aTChris
    6 years ago

    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 

     

aTChris's avatar
aTChris
Resolver I
6 years ago

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's avatar
primolee
Helper V
6 years ago
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!