Forum Discussion

muneebch041

Frequent Visitor

4 years ago

Solved

Connect SharePoint Custom List Data with Power BI using O Data

http://<sitename>/pwa/site/_api/Projectdata

The above-given link helps me to connect project online complete data with Power BI.

Issue:

I am unable to find the Custom list of Project Online using the above-given link.

I am able to access Issues and Risk. But the custom lists are not available in the data.

Looking forward to every one response!

Need Help

Show and Tell

Tips and Tricks

muneebch041
4 days ago
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"

3 Replies

Anonymous
Not applicable
4 years ago
Hi muneebch041 ,

You can refer the following links to get it:

Power BI with SharePoint data: The Ultimate Guide

https://siteurl/_vti_bin/listdata.svc

Power BI - Get Data From SharePoint List / Library

Get SharePoint List/Library Using OData Feed

The Open Data Protocol (OData) is a data access protocol for the web.

OData provides a uniform way to query and manipulates datasets through CRUD operations (create, read, update, and delete).

OData defines an abstract data model and a protocol that let the client access the information exposed by SharePoint.

Steps

Open Power BI Desktop and at Home Tab, click on Get Data > OData Feed.

Type the OData Feed URL for the SharePoint "http://siteurl/_vti_bin/listdata.svc" and click Connect.

ListData.svc provides a way of getting information from a list using REST.

In Navigator dialog, select the required list and click Load.

You should now find the list fields in Fields section, and you should be ready to build your report from SharePoint list items.

Note
In case the current user is not authorized to connect to the site, you will be prompted to set the right credential as shown below.

Best Regards
- muneebch041
  Frequent Visitor
  4 years ago
  Hi,
  Thank you for providing your valuable feedback.
  But my query is still for custom list. I can access the predefined lists of projects online using the above-given link but unable to access the custom lists.
muneebch041
Frequent Visitor
4 days ago
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"

Forum Discussion

Connect SharePoint Custom List Data with Power BI using O Data

3 Replies

Related content

OData Error When Connecting to SharePoint Online

Sharepoint Connection

Connecting to sharepoint

Connecting Sharepoint Folder to PowerBI

Sharepoint Folder Connection – Access Required