Connect SharePoint Custom List Data with Power BI using O Data

So guys here the solution for that

let

// ================= OPTIONS (edit these) =================

SiteRoot = "yoursitename/pwa/site/",

ListTitle = "Custom List Name",

// Fields to pull back from the Projects endpoint

ProjectFields = "ProjectName,ProjectWorkspaceInternalUrl",

// Server-side filter/expand pushed down to the Sprint list query

FilterOData = "", // e.g. add: " and Status ne 'Completed'"

ExpandOData = "", // e.g. "Author,Editor,Assignee"

// HTTP status codes treated as "no access / not found" -> silently skip project

SkipStatus = {401, 403, 404, 500},

// ================= PROJECT LIST (filtered at source) =================

ProjectsResponse = Web.Contents(SiteRoot, [

RelativePath = "_api/ProjectData/Projects",

Headers = [Accept = "application/json;odata=nometadata"],

Query = [#"$select" = ProjectFields]

]),

ProjectsJson = Json.Document(ProjectsResponse),

ProjectsList = try ProjectsJson[value] otherwise ProjectsJson[d][results],

ProjectsTable = Table.FromRecords(ProjectsList),

#"Filtered Rows" = Table.SelectRows(ProjectsTable, each ([ProjectWorkspaceInternalUrl] <> null)),

#"Selected Columns" = Table.SelectColumns(#"Filtered Rows", Text.Split(ProjectFields, ",")),

#"Added RelPath" = Table.AddColumn(#"Selected Columns", "RelPath", each

let

Raw = Text.From([ProjectWorkspaceInternalUrl]? ?? ""),

Path = if Text.StartsWith(Raw, SiteRoot)

then Text.Replace(Raw, SiteRoot, "")

else Uri.EscapeDataString(Text.From([ProjectName]))

in

Text.TrimEnd(Path, {"/"}), type text),

// ================= FETCH SPRINT ITEMS PER PROJECT =================

// No-access / missing-list projects are silently skipped — no prompt, no error

#"Added ItemsRaw" = Table.AddColumn(#"Added RelPath", "ItemsRaw", each

let

Response = try Web.Contents(SiteRoot, [

RelativePath = [RelPath] & "/_api/web/Lists/getbytitle('" & ListTitle & "')/items",

Headers = [Accept = "application/json;odata=nometadata"],

Query = Record.Combine({

if FilterOData <> "" then [#"$filter" = FilterOData] else [],

if ExpandOData <> "" then [#"$expand" = ExpandOData] else []

}),

ManualStatusHandling = SkipStatus

]) otherwise null,

Status = if Response = null then null else Value.Metadata(Response)[Response.Status]?,

Result = if Status = 200 then Json.Document(Response) else null

in

Result

),

#"Filtered Valid Responses" = Table.SelectRows(#"Added ItemsRaw", each [ItemsRaw] <> null),

#"Added Items" = Table.AddColumn(#"Filtered Valid Responses", "Items", each [ItemsRaw][value]? ?? {}),

#"Removed Extra Columns" = Table.RemoveColumns(#"Added Items", {"ItemsRaw", "RelPath", "ProjectWorkspaceInternalUrl"}),

#"Expanded Items" = Table.ExpandListColumn(#"Removed Extra Columns", "Items"),

// ================= EXPAND SPRINT ITEM COLUMNS =================

#"Expanded Items1" = Table.ExpandRecordColumn(#"Expanded Items", "Items", {

"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ID",

"ContentTypeId", "Title", "Modified", "Created", "AuthorId", "EditorId",

"OData__UIVersionString", "Attachments", "GUID", "ComplianceAssetId", "Subject",

"Start_x0020_Date", "Due_x0020_Date", "Status", "Private", "Description",

"WatchersId", "WatchersStringId", "Comments", "AssigneeId", "AssigneeStringId"

}, {

"Items.FileSystemObjectType", "Items.Id.1", "Items.ServerRedirectedEmbedUri", "Items.ServerRedirectedEmbedUrl", "Items.ID",

"Items.ContentTypeId", "Items.Title", "Items.Modified", "Items.Created", "Items.AuthorId", "Items.EditorId",

"Items.OData__UIVersionString", "Items.Attachments", "Items.GUID", "Items.ComplianceAssetId", "Items.Subject",

"Items.Start_x0020_Date", "Items.Due_x0020_Date", "Items.Status", "Items.Private", "Items.Description",

"Items.WatchersId", "Items.WatchersStringId", "Items.Comments", "Items.AssigneeId", "Items.AssigneeStringId"

})

in

#"Expanded Items1"