Cannot refresh dataset since dynamic data sources aren't refreshed. source is sharepoint list

[s-roberts]

We need to report the sharepoint list version history in PowerBI.

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Using Criss Webb's blog post i did manage to impor the data into the PoweBI desktop, and elimenate the dynamic datasource error in the Data Source settings, when refreshing in PBI Service. 

I now have a valid web source connector in PowerBI desktop, that reads the version history of each item of a sharepoint list.

This all works in the PBI Desktop but again, there's an error in the PBI service when I try to refresh. 

It does not accept my sharepoint credentials to authentcate. 

Is there a working method to fetch sharepoint list history in PBI with the ability to refresh in the service??

Thanks

[s-roberts · Accepted solution]

Got it to work. This is my function: 

let
Source = (VersionsRelevantItemID as number) => let
VRI = Text.From(VersionsRelevantItemID),
END_PATH = Text.Combine({"items(", VRI, ")/versions"}),
Source = Xml.Tables(Web.Contents(
"https://#####.sharepoint.com/sites/###/_api/web/Lists/getbytitle('sharepointlistname')", [RelativePath = END_PATH])),
entry = Source{0}[entry],
#"Removed Other Columns2" = Table.SelectColumns(entry,{"content"}),
#"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns2", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),
#"Expanded content1" = Table.ExpandTableColumn(#"Expanded content", "content", {"properties"}, {"properties"}),
#"Expanded properties" = Table.ExpandTableColumn(#"Expanded content1", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})
in
#"Expanded properties"
in
Source

[Mandaar_Mantri]

Thanks. This solution has helped me.

[GilbertQ]

Hi @s-roberts 

Do you get this error in the power BI Service?

I have used Chris Webb method in the past and it has worked.

What is the URL that you are trying to use?

[s-roberts]

In the PBI Service, the web connector does not authenticate. It should be the same credentials as for the sharepoint connectors. 

This is my function: 

let

    Source = (Path) =>

    let

    Host = "https://#####.sharepoint.com/sites/ProgramProcess",

        Source = Xml.Tables(Web.Contents( Host, [RelativePath = Path])),

            entry = Source{0}[entry],

        #"Removed Other Columns2" = Table.SelectColumns(entry,{"content"}),

        #"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns2", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),

        #"Expanded content1" = Table.ExpandTableColumn(#"Expanded content", "content", {"properties"}, {"properties"}),

        #"Expanded properties" = Table.ExpandTableColumn(#"Expanded content1", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})

    in         #"Expanded properties"

in    Source

The Path variable is generated in another query of PCO list items. 

each Path would look like this:  /_api/web/Lists/getbytitle('PCO List')/items(###)/versions

Thanks for your help