Dynamic data source using Sharepoint

[SJHA]

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"

[V-yubandi-msft]

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 · Accepted solution]

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"

[V-yubandi-msft]

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.

[lbendlin]

Using OData.Feed and the Query option to avoid the dynamic data sources error in Power BI

[SJHA]

@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]

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'.