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

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,