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 

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

       

      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's avatar
        aTChris
        Resolver I
        6 years ago

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