Forum Discussion
SharePoint list query alternative or optimization
hjaf FYI that I finally made a video to describe this approach, and am adding it here for others that may find this post. It also gets the count of items and makes the right number of API calls.
Get SharePoint List Data with Power BI ... Fast - YouTube
Also, a reminder to mark one of these as the solution.
Regards,
Pat
Are you just looking in the preview, or some other filter applied? Can you share your query?
I checked in both Query Editor and Data view. It is the same. There are no filters applied. I am using the query pasted below:
let
baseurl = "SHAREPOINT LIST LINK_api/web/lists/getbytitle('LIST NAME')/",
itemcount = Json.Document(Web.Contents("SHAREPOINT LIST LINK_api/web/lists/getbytitle('LIST NAME')/"&"ItemCount", [Headers=[Accept="application/json"]]))[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount/5000), 5000),
#"Converted to Table" = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Skip"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Skip", type text}}),
fieldselect = "&$top=2000", // all fields with no expansion
//fieldselect = "&$top=5000&$select = Id,Title,Person,Date", // list desired fields (no expansion)
//fieldselect = "&$top=5000&$select=Id,Title,Choice,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus,Date,Person/LastName,Person/FirstName,Person/EMail&$expand=LookupColumn,Person",
//Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents("baseurl" & "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, [Headers=[Accept="application/json"]]))),
Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(
"SHAREPOINT LIST LINK_api/web/lists/getbytitle('LIST NAME')/",
[
RelativePath="/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect,
Headers=[Accept="application/json"]
]
)
)
),
#"Expanded Items" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
#"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value"),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded value",{"value"}),
#"Expanded value1" = Table.ExpandRecordColumn(#"Removed Other Columns", "value", {"EmployeeEmailID", "WeekStart", "WeekStartNum", "DayOfWeek", "DayOfWeekNum", "EmployeeReportingToEmailID", "ActualWorkHours", "ApprovalStatus", "MasterID_Weekly", "TotalHr", "Modified", "Created", "ID"}, {"EmployeeEmailID", "WeekStart", "WeekStartNum", "DayOfWeek", "DayOfWeekNum", "EmployeeReportingToEmailID", "ActualWorkHours", "ApprovalStatus", "MasterID_Weekly", "TotalHr", "Modified", "Created", "ID"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded value1",{{"WeekStart", type datetime}, {"DayOfWeek", type datetime}, {"Created", type datetime}, {"Modified", type datetime}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type2",{{"ID", type number}, {"WeekStart", type date}, {"WeekStartNum", type number}, {"DayOfWeek", type date}, {"DayOfWeekNum", type number}, {"ActualWorkHours", type number}, {"Modified", type date}, {"Created", type date}})
in
#"Changed Type1"