SharePoint list query alternative or optimization

Thank you times a bazillion.

Thank you, your video helped me. After I moved to version 2 everything was faster.

first of all you are a crack, My query is the following, I am applying your code and I have a very large sp list.... and it returns me 150000 elements but they are duplicates please could you help me

let
sitename ="<your site>", // if a subsite use "Site/SubSite"
listname = "BigList",
baseurl = "https://<your SharePoint URL>/sites/" & sitename & "/_api/web/lists/GetByTitle('" & listname & "')/",
itemcount = Json.Document(Web.Contents(baseurl&"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=5000", // 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"]]))),
#"Expanded Items" = Table.ExpandRecordColumn(Custom1, "Items", {"value"}, {"value"}),
#"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value")
in
#"Expanded value"

mahoneypat Awesome!
yes, I most definately need to use pagination 🙂 near 20k items in the lists 🙂 

Someone in this forum said to look up a solution here: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

I did so, and after reading a number of posts on the subject I was able to get it working in PowerBI online service.  I won't bore you with a lot of details, here's just a simple modified M Code you need to use instead.

What this does is uses a static URL for the first parameter of the Web.Contents() function, then uses a little known (apparently) 2nd parameter to actually pass the rest of the URL (relative path) and query/value pairs into the query string.

You should be able to just modify the bold parts of this and be ready to go.  I modified this from the original on the blog post and it even has the original commented-out bits. You can remove those if you want. 

I actually hard-coded most of the paths instead of using baseurl or other variables. I probably could have still used them but I wanted immediate success 🙂

This has been an aboslute lifesaver to me. Now my list with 120K list items only takes 15 seconds to refresh.

let
baseurl = "https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/",
itemcount = Json.Document(Web.Contents("https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/"&"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=5000", // 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(
"https://TENANT.sharepoint.com/sites/SITE/_api/web/lists/GetByTitle('LIST')/",
[
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")
in
#"Expanded value"

I got this to work.

This fixed it for me. It was not refreshing as PBI App was recognising it as dynamid data source. Replaced Custom1 step with this: Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl, [RelativePath = "/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect, Headers=[Accept="application/json"]]))), 

It works all right for me