Forum Discussion

s-roberts's avatar
s-roberts
Icon for Helper I rankHelper I
5 years ago
Solved

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

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 And Power BI M Code Chris Webb'...
Need Help
  • s-roberts's avatar
    s-roberts
    5 years ago

    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

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
5 years ago

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's avatar
s-roberts
Icon for Helper I rankHelper I
5 years ago

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