Forum Discussion

Toll's avatar
Toll
Icon for Helper I rankHelper I
3 years ago

Scheduled Refresh with Web/SharePoint Folder connector pointing to Excel in SharePoint

Hello, I am trying to publish a report and configure scheduled refresh. The dataset is comprised of some SharePoint Online List connectors and some OData Feed connectors as well as a connection to ...
Need Help
ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
3 years ago

Hi. A dynamic data source usually means that your source doesn't have a static definition. It means you might be changing the url for the source. For example : one of the most frequent dynamics sources is when using Web.Contests for getting data from API or URLs we tend to use parameters like 

Web.Contents("https://web.com/whatever?year=" & param)

Desktop will just run the query, but Service want to identify the unique source and can't, because it doesn't know if it's "https://web.com/whatever?year=2022" or "https://web.com/whatever?year=2023" when running the query.

We might be able to help more if you share the source power query code for that.

In order to fix the previous message I have send it is recommended to use RelativePath. You can check the practice in the following post: https://blog.ladataweb.com.ar/post/630597294839955456/powerquery-buena-práctica-para-un-web-request

I hope that helps,

  • Toll's avatar
    Toll
    Icon for Helper I rankHelper I
    3 years ago

    Hello,

    Thank you for your reply. Sorry if I have misunderstood, but the only query using Web.Contents() is below:

     

    let    
    source = Excel.Workbook(Web.Contents("https://[site].com/.../Deliverables/Power%20BI%20Reporting/File.xlsx"), null, true),
    Table1_Table = source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Team", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Name"}, dimUsers, {"Name"}, "dimUsers", JoinKind.LeftOuter),
    #"Expanded dimUsers" = Table.ExpandTableColumn(#"Merged Queries", "dimUsers", {"Users.ID"}, {"dimUsers.Users.ID"})
in
    #"Expanded dimUsers"

     

    But that was also because using:

     Source = SharePoint.Files

    Didn't work either..

    • ibarrau's avatar
      ibarrau
      Icon for Super User rankSuper User
      3 years ago

      I don't think that's the one with a mistake because it looks like it points to one url. The way of connecting it's perfect, you don't worry. What other table sources do you have in there? there is a dim users, what data sources are you using?

      Regards

      • Toll's avatar
        Toll
        Icon for Helper I rankHelper I
        3 years ago

        Thank you for explaining.

        dimUsers points to a parameter then basically does a "for each" loop on multiple SharePoint URLs, separated by commas. I assume this is the problem now.

         

        Is the only way to fix this by hardcoding the URL and creating multiple queries for each SharePoint URL? Or is there a better way?

         

        let
    SharePointURLParameter = SharePointList,
    SharePointURLs = Text.Split(SharePointURLParameter, ","),
    appendedURLs = List.Transform(SharePointURLs, each _ & "/_vti_bin/listdata.svc"),

    createQuery = (url) =>
        let
            ODataSource = OData.Feed(url, null, [Implementation="2.0"]),
            userinfoTable = ODataSource{[Name="UserInformationList", Signature="table"]}[Data],
            appendedTable = Table.AddColumn(userinfoTable, "CustomerSharePointCode", each Text.BetweenDelimiters(Text.AfterDelimiter(url, "/cases/"), "/", "/_vti_bin/listdata.svc")),
            combinedColumn_TeamID = Table.AddColumn(appendedTable, "CombinedID", each Text.Combine({Text.From([Id]), [CustomerSharePointCode]}, "-"))
        in
            combinedColumn_TeamID,

    createQueries = List.Transform(appendedURLs, each createQuery(_)),
    combinedQuery = if List.Count(createQueries) > 0 then Table.Combine(createQueries) else #table({"DummyColumn"},{}),
    #"Removed Other Columns" = Table.SelectColumns(combinedQuery,{"CombinedID", "Name","WorkEmail","ContentType"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"CombinedID", "Users.ID"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"ContentType"})
in
    #"Removed Columns"

        the refresh works perfectly in Power BI desktop ☹️ but I suppose the service can't determine the URLs via parameter