Forum Discussion

SJHA's avatar
SJHA
Helper I
1 year ago
Solved

Dynamic data source using Sharepoint

I'm trying to get some customized fields (Owner, CoOwner) on the sitepages but I'm struggling to find the way to do it properly.

The best way so far that gives me the EMail of Owner and CoOwner has been using Sharepoint.Tables or OData.

I tried to get the data from GRAPH APIs without success.

 

I have a table with the URLs that I need data from and I'd like to use this as more can be added or removed without my knowledge.
Most recent try is to save this function in PowerBI dataflow:

let
    FetchData = (url as text) =>
    let
        Source = OData.Feed(
        url & "/_api/web/lists/getbytitle('Webstedssider')/items?"
        & "$select=ContentType,ServerUrl,Owner/EMail,CoOwner/EMail,Modified,Modified_x0020_By&$expand=Owner,CoOwner"
        )
    in
        Source
in
    FetchData
 
I'm open to explore other methods, but my permissions are limited to Sharepoint and Graph.
_______________________________________________
Edit: I tried to marked my reply as the solution, but it got deleted. Here's the script I used:

let
// Parameters
BaseUrl = "https://graph.microsoft.com/v1.0/",
AccessToken = AccessTokenTable{0}[AccessToken],
SiteIds = siteId[id],
LibIds = siteId[docListId],
// Combine SiteIds and LibIds into a single list of records
SiteLibPairs = Table.ToRecords(Table.FromColumns({SiteIds, LibIds}, {"siteId", "libId"})),
// Function to get pages for a single site
GetSitePages = (siteLibPair as record) as table =>
let
siteId = siteLibPair[siteId],
libId = siteLibPair[libId],
RelativePath = "sites/" & siteId & "/lists/" & libId & "/items?$expand=fields",
Response = Json.Document(Web.Contents(BaseUrl, [
RelativePath = RelativePath,
Headers = [Authorization = "Bearer " & AccessToken]
])),
Pages = try Table.FromRecords(Response[value]) otherwise Table.FromRecords({})
in
Pages,
// Function to get user info
GetUserInfo = (siteId as text) as table =>
let
RelativePath = "sites/" & siteId & "/lists/Liste med brugeroplysninger/items?$expand=fields($select=SipAddress)&$top=99999",
Response = Json.Document(Web.Contents(BaseUrl, [
RelativePath = RelativePath,
Headers = [Authorization = "Bearer " & AccessToken]
])),
UserInfo = try Table.FromRecords(Response[value]) otherwise Table.FromRecords({}),
ExpandedUserInfo = if Table.HasColumns(UserInfo, "fields") then Table.ExpandRecordColumn(UserInfo, "fields", {"SipAddress"}) else UserInfo
in
ExpandedUserInfo,
// Function to replace lookup IDs with SIP addresses
ReplaceLookupIds = (sitePages as table, userInfoMap as list) as table =>
let
ExpandedSitePages = if Table.HasColumns(sitePages, "fields") then Table.ExpandRecordColumn(sitePages, "fields", {"LinkFilename", "OwnerLookupId", "CoOwnerLookupId"}) else sitePages,
ReplacedLookupIds = if Table.HasColumns(ExpandedSitePages, "OwnerLookupId") and Table.HasColumns(ExpandedSitePages, "CoOwnerLookupId") then
Table.TransformColumns(ExpandedSitePages, {
{"OwnerLookupId", each if _ = null then null else List.First(List.Select(userInfoMap, (x) => x[id] = _))[SipAddress], type nullable text},
{"CoOwnerLookupId", each if _ = null then null else List.First(List.Select(userInfoMap, (x) => x[id] = _))[SipAddress], type nullable text}
})
else
ExpandedSitePages
in
ReplacedLookupIds,
// Process each site individually
ProcessedSitePages = List.Transform(SiteLibPairs, each
let
siteId = _[siteId],
userInfoMap = Table.ToRecords(GetUserInfo(siteId)),
sitePages = GetSitePages(_)
in
ReplaceLookupIds(sitePages, userInfoMap)
),
// Combine all processed site pages
CombinedSitePages = Table.Combine(ProcessedSitePages),
// Additional transformations
#"Expanded lastModifiedBy" = Table.ExpandRecordColumn(CombinedSitePages, "lastModifiedBy", {"user"}, {"lastModifiedBy.user"}),
#"Expanded lastModifiedBy.user" = Table.ExpandRecordColumn(#"Expanded lastModifiedBy", "lastModifiedBy.user", {"email"}, {"lastModifiedBy"}),
#"Expanded parentReference" = Table.ExpandRecordColumn(#"Expanded lastModifiedBy.user", "parentReference", {"siteId"}, {"parentRef.siteId"}),
#"Expanded contentType" = Table.ExpandRecordColumn(#"Expanded parentReference", "contentType", {"name"}, {"contentType"})
in
#"Expanded contentType"
Need Help
  • SJHA's avatar
    SJHA
    1 year ago

    I've resolved the issue using a different method. Using Graph API and relativePath method.

    Get list items and replace the lookupID with their 'SIPaddress' from 'User information list' - this works smoothly with any lists like sitepages and documents.

     

    let
    // Parameters
    BaseUrl = "https://graph.microsoft.com/v1.0/",
    AccessToken = AccessTokenTable{0}[AccessToken],
    SiteIds = siteId[id],
    LibIds = siteId[docListId],
    // Combine SiteIds and LibIds into a single list of records
    SiteLibPairs = Table.ToRecords(Table.FromColumns({SiteIds, LibIds}, {"siteId", "libId"})),
    // Function to get pages for a single site
    GetSitePages = (siteLibPair as record) as table =>
    let
    siteId = siteLibPair[siteId],
    libId = siteLibPair[libId],
    RelativePath = "sites/" & siteId & "/lists/" & libId & "/items?$expand=fields",
    Response = Json.Document(Web.Contents(BaseUrl, [
    RelativePath = RelativePath,
    Headers = [Authorization = "Bearer " & AccessToken]
    ])),
    Pages = try Table.FromRecords(Response[value]) otherwise Table.FromRecords({})
    in
    Pages,
    // Function to get user info
    GetUserInfo = (siteId as text) as table =>
    let
    RelativePath = "sites/" & siteId & "/lists/Liste med brugeroplysninger/items?$expand=fields($select=SipAddress)&$top=99999",
    Response = Json.Document(Web.Contents(BaseUrl, [
    RelativePath = RelativePath,
    Headers = [Authorization = "Bearer " & AccessToken]
    ])),
    UserInfo = try Table.FromRecords(Response[value]) otherwise Table.FromRecords({}),
    ExpandedUserInfo = if Table.HasColumns(UserInfo, "fields") then Table.ExpandRecordColumn(UserInfo, "fields", {"SipAddress"}) else UserInfo
    in
    ExpandedUserInfo,
    // Function to replace lookup IDs with SIP addresses
    ReplaceLookupIds = (sitePages as table, userInfoMap as list) as table =>
    let
    ExpandedSitePages = if Table.HasColumns(sitePages, "fields") then Table.ExpandRecordColumn(sitePages, "fields", {"LinkFilename", "OwnerLookupId", "CoOwnerLookupId"}) else sitePages,
    ReplacedLookupIds = if Table.HasColumns(ExpandedSitePages, "OwnerLookupId") and Table.HasColumns(ExpandedSitePages, "CoOwnerLookupId") then
    Table.TransformColumns(ExpandedSitePages, {
    {"OwnerLookupId", each if _ = null then null else List.First(List.Select(userInfoMap, (x) => x[id] = _))[SipAddress], type nullable text},
    {"CoOwnerLookupId", each if _ = null then null else List.First(List.Select(userInfoMap, (x) => x[id] = _))[SipAddress], type nullable text}
    })
    else
    ExpandedSitePages
    in
    ReplacedLookupIds,
    // Process each site individually
    ProcessedSitePages = List.Transform(SiteLibPairs, each
    let
    siteId = _[siteId],
    userInfoMap = Table.ToRecords(GetUserInfo(siteId)),
    sitePages = GetSitePages(_)
    in
    ReplaceLookupIds(sitePages, userInfoMap)
    ),
    // Combine all processed site pages
    CombinedSitePages = Table.Combine(ProcessedSitePages),
    // Additional transformations
    #"Expanded lastModifiedBy" = Table.ExpandRecordColumn(CombinedSitePages, "lastModifiedBy", {"user"}, {"lastModifiedBy.user"}),
    #"Expanded lastModifiedBy.user" = Table.ExpandRecordColumn(#"Expanded lastModifiedBy", "lastModifiedBy.user", {"email"}, {"lastModifiedBy"}),
    #"Expanded parentReference" = Table.ExpandRecordColumn(#"Expanded lastModifiedBy.user", "parentReference", {"siteId"}, {"parentRef.siteId"}),
    #"Expanded contentType" = Table.ExpandRecordColumn(#"Expanded parentReference", "contentType", {"name"}, {"contentType"})
    in
    #"Expanded contentType"

6 Replies

Replies have been turned off for this discussion
    • SJHA's avatar
      SJHA
      Helper I
      1 year ago

      lbendlin V-yubandi-msft Thanks for the sources.
      I've tried that and it works fine when it's just one link, but as soon as I loop through a list of URLs, then it 'triggers' the Dynamic Data Source error. This works fine in Desktop but the semantic model can't be refreshed.

       

      let
      siteUrls = OAsites[Title], // This gets all site URLs as a list

      // Function to fetch data from a given site URL
      FetchData = (url as text) =>
      let
      Source = OData.Feed(
      url &
      "/_api/web/lists/getbytitle('Webstedssider')/items?$select=ContentType,ServerUrl,Owner/EMail,CoOwner/EMail,Modified,Modified_x0020_By&$expand=Owner,CoOwner",
      null,
      [
      Implementation = "2.0",
      Query = [#"debug-mode" = "true"]
      ]
      )
      in
      Source,

      // Apply function to all site URLs and combine results
      AllData = List.Transform(siteUrls, each FetchData(_)),

      // Convert list of tables into a single table
      CombinedData = Table.Combine(AllData),
      #"Expanded ContentType" = Table.ExpandRecordColumn(CombinedData, "ContentType", {"Name"}, {"ContentType.Name"}),
      #"Expanded Owner" = Table.ExpandRecordColumn(#"Expanded ContentType", "Owner", {"EMail"}, {"Owner.EMail"}),
      #"Expanded CoOwner" = Table.ExpandRecordColumn(#"Expanded Owner", "CoOwner", {"EMail"}, {"CoOwner.EMail"})
      in
      #"Expanded CoOwner"

      • V-yubandi-msft's avatar
        V-yubandi-msft
        Community Support
        1 year ago

        Hi SJHA ,

        Since Power BI service does not support dynamically generated URLs within the query, particularly concerning data refresh operations, a dynamic data source - where certain connection details are determined at runtime - poses a challenge for Power BI to handle during scheduled refreshes.

         

         

        FYI: Data refresh in Power BI - Power BI | Microsoft Learn

         

        Alternate Workaround:

        As Power BI Service doesn't support dynamically generated URLs within queries, it's recommended to use a parameterized approach. This means defining a base URL as a parameter and dynamically adjusting the request within the query to ensure both compatibility and refresh ability.
        Reference Link: Parameters - Power Query | Microsoft Learn

         

        If my response solved your query, please mark it as the Accepted solution to help others find it easily.

        And if my answer was helpful, I'd really appreciate a 'Kudos'.

         

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    Hello SJHA ,

    Thank you for reaching out to us on the Microsoft Fabric Community Forum.  lbendlin Thank you for your insight.

    To dynamically retrieve custom fields (Owner, CoOwner) from SharePoint Site Pages in Power BI dataflows, adhere to this structured approach as outlined in Microsoft documentation.

     

    1. The recommended method for accessing SharePoint list data, including Site Pages, is using the OData Feed connector.

     

     

    Link: Power Query OData Feed connector - Power Query | Microsoft Learn.

     

    2. If OData does not function as anticipated, please consider using the SharePoint List connector.

     

    Link: Power Query SharePoint list connector - Power Query | Microsoft Learn

     

    The SharePoint List connector is compatible with standard SharePoint permissions and is straightforward to configure.

     

    If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

     

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    Hi SJHA ,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.

    • SJHA's avatar
      SJHA
      Helper I
      1 year ago

      I've resolved the issue using a different method. Using Graph API and relativePath method.

      Get list items and replace the lookupID with their 'SIPaddress' from 'User information list' - this works smoothly with any lists like sitepages and documents.

       

      let
      // Parameters
      BaseUrl = "https://graph.microsoft.com/v1.0/",
      AccessToken = AccessTokenTable{0}[AccessToken],
      SiteIds = siteId[id],
      LibIds = siteId[docListId],
      // Combine SiteIds and LibIds into a single list of records
      SiteLibPairs = Table.ToRecords(Table.FromColumns({SiteIds, LibIds}, {"siteId", "libId"})),
      // Function to get pages for a single site
      GetSitePages = (siteLibPair as record) as table =>
      let
      siteId = siteLibPair[siteId],
      libId = siteLibPair[libId],
      RelativePath = "sites/" & siteId & "/lists/" & libId & "/items?$expand=fields",
      Response = Json.Document(Web.Contents(BaseUrl, [
      RelativePath = RelativePath,
      Headers = [Authorization = "Bearer " & AccessToken]
      ])),
      Pages = try Table.FromRecords(Response[value]) otherwise Table.FromRecords({})
      in
      Pages,
      // Function to get user info
      GetUserInfo = (siteId as text) as table =>
      let
      RelativePath = "sites/" & siteId & "/lists/Liste med brugeroplysninger/items?$expand=fields($select=SipAddress)&$top=99999",
      Response = Json.Document(Web.Contents(BaseUrl, [
      RelativePath = RelativePath,
      Headers = [Authorization = "Bearer " & AccessToken]
      ])),
      UserInfo = try Table.FromRecords(Response[value]) otherwise Table.FromRecords({}),
      ExpandedUserInfo = if Table.HasColumns(UserInfo, "fields") then Table.ExpandRecordColumn(UserInfo, "fields", {"SipAddress"}) else UserInfo
      in
      ExpandedUserInfo,
      // Function to replace lookup IDs with SIP addresses
      ReplaceLookupIds = (sitePages as table, userInfoMap as list) as table =>
      let
      ExpandedSitePages = if Table.HasColumns(sitePages, "fields") then Table.ExpandRecordColumn(sitePages, "fields", {"LinkFilename", "OwnerLookupId", "CoOwnerLookupId"}) else sitePages,
      ReplacedLookupIds = if Table.HasColumns(ExpandedSitePages, "OwnerLookupId") and Table.HasColumns(ExpandedSitePages, "CoOwnerLookupId") then
      Table.TransformColumns(ExpandedSitePages, {
      {"OwnerLookupId", each if _ = null then null else List.First(List.Select(userInfoMap, (x) => x[id] = _))[SipAddress], type nullable text},
      {"CoOwnerLookupId", each if _ = null then null else List.First(List.Select(userInfoMap, (x) => x[id] = _))[SipAddress], type nullable text}
      })
      else
      ExpandedSitePages
      in
      ReplacedLookupIds,
      // Process each site individually
      ProcessedSitePages = List.Transform(SiteLibPairs, each
      let
      siteId = _[siteId],
      userInfoMap = Table.ToRecords(GetUserInfo(siteId)),
      sitePages = GetSitePages(_)
      in
      ReplaceLookupIds(sitePages, userInfoMap)
      ),
      // Combine all processed site pages
      CombinedSitePages = Table.Combine(ProcessedSitePages),
      // Additional transformations
      #"Expanded lastModifiedBy" = Table.ExpandRecordColumn(CombinedSitePages, "lastModifiedBy", {"user"}, {"lastModifiedBy.user"}),
      #"Expanded lastModifiedBy.user" = Table.ExpandRecordColumn(#"Expanded lastModifiedBy", "lastModifiedBy.user", {"email"}, {"lastModifiedBy"}),
      #"Expanded parentReference" = Table.ExpandRecordColumn(#"Expanded lastModifiedBy.user", "parentReference", {"siteId"}, {"parentRef.siteId"}),
      #"Expanded contentType" = Table.ExpandRecordColumn(#"Expanded parentReference", "contentType", {"name"}, {"contentType"})
      in
      #"Expanded contentType"