Forum Discussion

s-roberts's avatar
s-roberts
Helper 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's BI Blog (crossjoin.co.uk)

 

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

 

 

 

 

 

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

4 Replies

  • s-roberts's avatar
    s-roberts
    Helper I
    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
    Super 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
      Helper 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