Dynamic data source using Sharepoint

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"