Forum Discussion

Toll's avatar
Toll
Helper 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
Super 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
Helper 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
    Super 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
      Helper 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

      • ibarrau's avatar
        ibarrau
        Super User
        3 years ago

        That's definitely the source with the issue. Power Bi Service can't specify the source for that OData. This is a tricky case because I'm not sure you can split url with good practices for ODataFeed... If you can get your data with Web.Contents instead of OData Feed, we might be able to build something like

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

        The example will help power bi to think the source is "web.com" even though you are changing the whole picture with the path. I don't think odata has that. If you can get your data with this other way, you might be able to avoid "Dynamic source" error.

        I hope that helps,