Refresh issue with SharePoint.Tables in function

[Anonymous]

I've realised that my design to update a report with the latest status of various Task lists on SharePoint online won't work out the box (I'm getting the dreaded "You can't schedule refresh for this dataset because one or more sources currently don't support refresh. " message) but I'm wondering whether there's a workaround or whether I need to go back to the drawing board and do a complete redesign.....

I have a function which is called to pull the data from various SharePoint Site Task Lists with two parameters List URL and Task List name:

let GetTasks=(HouseURL as text, TaskList as text) as table =>

let
    Source = SharePoint.Tables(HouseURL, [ApiVersion = 14]),
    Tasks1 = Source{[Name=TaskList]}[Content],

    #"Removed Duplicates" = Table.Distinct(#"Tasks1", {"TaskName", Comparer.OrdinalIgnoreCase}),
    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"TaskName", "PriorityValue", "TaskStatusValue", "StartDate", "DueDate", "Completed", "Id"}),
    #"Extracted Date" = Table.TransformColumns(#"Removed Other Columns",{{"StartDate", DateTime.Date}, {"DueDate", DateTime.Date}}),
    #"Sorted Rows" = Table.Sort(#"Extracted Date",{{"TaskStatusValue", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([TaskStatusValue] = "Completed")),
    #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"DueDate", Order.Ascending}}),
    #"Kept Last Rows" = Table.LastN(#"Sorted Rows1", 1),
    #"Filtered Not Completed" = Table.SelectRows(#"Sorted Rows", each ([TaskStatusValue] <> "Completed")),
    #"Sorted Rows2" = Table.Sort(#"Filtered Not Completed",{{"DueDate", Order.Ascending}}),
    #"Kept First Rows" = Table.FirstN(#"Sorted Rows2",3),
    #"Sorted Rows3" = Table.Sort(#"Kept First Rows",{{"Id", Order.Ascending}}),
    #"Appended Query" = Table.Combine({#"Kept Last Rows", #"Sorted Rows3"}),
    #"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 1, 1)
in
    #"Added Index"

in GetTasks

This works beautifully in Power BI Desktop, but doesn't in the service. 😕

All sites reside on the same base URL, so credentials for one will work for all the others.

I've had a look at various posts here and on Chris Webb's blog, but nothing gives me that aha! moment 😞

Any ideas, workarounds, fixes?

Thanks.

Ian

[v-caliao-msft]

Hi @Anonymous,

For this error message
 "You can't schedule refresh for this dataset because one or more sources currently don't support refresh. "

It indicates that the data source is not a supported schedule refresh data source. Currently, there is no other approach to work around this feature.

If you have any concern on this feature, you can submit your idea on the link below. If this feature was mentioned by mulitple users, product team will consider to add this feature to next release.
https://ideas.powerbi.com/forums/265200-power-bi-ideas

Regards,

Charlie Liao