Hi all,
I'm not sure if this should be in a forum for PBI or SharePoint - I have two issues, let me explain.
I'm attempting to pull data from a SharePoint list using the rest API.
Issue 1
When I visit the list in my browser and export it to Excel, I get around 3,000 records.
However, when I import it into PBI via the API I get over 24,000 records.
I think this is because in the browser I am looking at a specific view of the data and I can't find a way to specify the view name when calling the API.
Is this possible?
Issue 2
This is most likely another symptom of the issue above - when I look at the data pulled into PBI via the API (this also happens when using SharePoint List or SharePoint Online List via the standard Get Data option), as well as having way too many rows lots of columns are missing.
This is my last throw of the dice and I'm really hoping some clever soul will know what I am doing wrong.
This is the code I am using - found elsewhere online because the internet is amazing.
let
siteurl = "https://sharepointsite.com/sites/SiteName",
listname = "Name%20Of%20List",
fieldselect = "&$top=5000", // all fields with no expansion
InitialWebCall = Json.Document(Web.Contents(siteurl, [RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$skipToken=Paged=TRUE" & fieldselect, Headers = [Accept = "application/json"]])),
datalist = List.Generate(() => InitialWebCall, each List.Count([value]) > 0, each try Json.Document(Web.Contents(siteurl, [RelativePath = "_api" & Text.AfterDelimiter([odata.nextLink], "_api"), Headers = [Accept = "application/json"]])) otherwise [value = {}], each [value]),
#"Converted to Table" = Table.FromList(datalist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"odata.type", "odata.id", "odata.etag", "odata.editLink", "FileSystemObjectType", "Id", "KPMG_x0020_ID", "Client", "serve", "Svc_x0020_Ctr", "Title", "Country", "Platform", "Period", "OPS", "TaskDueDate", "Control_x0020_Number", "Control_x0020_Area", "Control_x0020_Objective", "Control_x0020_words", "Control_x0020_Summary", "Body", "Comments", "Status", "NGA_x0020_Comment_x0020_route", "NGA_x0020_Status", "AssignedToId", "Control_x0020_OwnerId", "Local_x0020_Process_x0020_OwnerId", "Delegate_x0020_Local_x0020_CheckId", "OData__x0031_st_x0020_Escalation_x0020Id", "Delegate_x0020_Escalation_x0020_Id", "Regional_x0020_CoordinatorId", "Auditor_x0020_Comments", "Auditor_x0020_Comment_x0020_Rout", "Auditor_x0020_Status", "Potential_x0020_Exception", "AuditorId", "Exception_x0020_or_x0020_PIO_x00", "Mangement_x0020_Response", "Due_x0020_Date", "Region", "Serving", "Process_x0020_Owner_x0020_Status", "Compliance_x0020_Status", "Process_x0020_Owner_x0020_Commen", "Compliance_x0020_Comments", "P_x002e_O_x0020_Comment_x0020_ro", "ISO_x0020_27001", "ISO_x0020_22301", "ISO_x0020_9001", "Copy_x0020_Status", "another_x0020_new_x0020_one", "Priority", "WorkflowName", "TPV", "NGA_x0020_Open_x0020_view", "Corrective_x0020_action", "CA_x0020_status", "CA_x0020_Due_x0020_Date", "CA_x0020_OwnerId", "ELT_x0020_Owner", "ELT_x0020_Owner_x0020_2", "Copy_x0020_to_x0020_CA_x0020_sta", "Report", "UK_x0020_or_x0020_ENT", "Global_x0020_ControlsId", "RelatedItems", "ContentTypeId", "AuthorId", "EditorId", "Modified", "Created", "WorkflowLink", "OffsiteParticipant", "OffsiteParticipantReason", "WorkflowOutcome", "GUID", "ExtAudit", "ExtAudit0", "Wave", "ID", "OData__UIVersionString", "Attachments"}, {"odata.type", "odata.id", "odata.etag", "odata.editLink", "FileSystemObjectType", "Id.1", "KPMG_x0020_ID", "Client", "serve", "Svc_x0020_Ctr", "Title", "Country", "Platform", "Period", "OPS", "TaskDueDate", "Control_x0020_Number", "Control_x0020_Area", "Control_x0020_Objective", "Control_x0020_words", "Control_x0020_Summary", "Body", "Comments", "Status", "NGA_x0020_Comment_x0020_route", "NGA_x0020_Status", "AssignedToId", "Control_x0020_OwnerId", "Local_x0020_Process_x0020_OwnerId", "Delegate_x0020_Local_x0020_CheckId", "OData__x0031_st_x0020_Escalation_x0020Id", "Delegate_x0020_Escalation_x0020_Id", "Regional_x0020_CoordinatorId", "Auditor_x0020_Comments", "Auditor_x0020_Comment_x0020_Rout", "Auditor_x0020_Status", "Potential_x0020_Exception", "AuditorId", "Exception_x0020_or_x0020_PIO_x00", "Mangement_x0020_Response", "Due_x0020_Date", "Region", "Serving", "Process_x0020_Owner_x0020_Status", "Compliance_x0020_Status", "Process_x0020_Owner_x0020_Commen", "Compliance_x0020_Comments", "P_x002e_O_x0020_Comment_x0020_ro", "ISO_x0020_27001", "ISO_x0020_22301", "ISO_x0020_9001", "Copy_x0020_Status", "another_x0020_new_x0020_one", "Priority", "WorkflowName", "TPV", "NGA_x0020_Open_x0020_view", "Corrective_x0020_action", "CA_x0020_status", "CA_x0020_Due_x0020_Date", "CA_x0020_OwnerId", "ELT_x0020_Owner", "ELT_x0020_Owner_x0020_2", "Copy_x0020_to_x0020_CA_x0020_sta", "Report", "UK_x0020_or_x0020_ENT", "Global_x0020_ControlsId", "RelatedItems", "ContentTypeId", "AuthorId", "EditorId", "Modified", "Created", "WorkflowLink", "OffsiteParticipant", "OffsiteParticipantReason", "WorkflowOutcome", "GUID", "ExtAudit", "ExtAudit0", "Wave", "ID", "OData__UIVersionString", "Attachments"})
in
#"Expanded Column2"
