SharePoint Online slow refresh

Bumping this

In that link, I provided some M code both in query form and in function form.  Below is the query form.  Just paste it into a blank query in Advanced Editor in the query editor over the existing text.  Replace the text in quotes in the site, tenant, and list.  Look at the URL for your SharePoint List for reference.  You may need to adapt the hard-coded part of the URL too if it doesn't match.  This will return a List, which you can click on to get a list of records, then convert that list to a table, then expand the records choosing the fields of interest.

let
Source =

let

site = "NameOfMySite",

tenant = "NameOfMyTenant",

list = "NameOfMyList",

getdata = Json.Document(Web.Contents("https://" & tenant & ".sharepoint.com/sites/" & site & "/_api/web/lists/GetByTitle('" & list & "')/items?$top=5000", [Headers=[Accept="application/json"]]))

in

getdata
in
Source

Note the above will return up to 5000 records.  If you want to use pagination to get many more, you need to use a M code and a web call like below (I haven't adapted the friendly form above to work like this yet).  Update the parts in <> (delete the <>).  This gets the count of items in your list, make a list of numbers up to that counting by 5000, makes each web call so you can expand all the result together. 

Note there are three rows called "fieldselect"; two should be commented out at any time.  You need to choose depending on if you have lookup/choice columns in your list.  There is example syntax in the latter two on how to choose which fields to expand in the return.

I still plan to write a blog post about this one day ...

let

    sitename ="<nameofyoursite", // if a subsite use "Site/SubSite"

    listname = "<nameofyourlist>",

    baseurl = "https://<yourtenantURL>/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,Person/LastName,Person/FirstName,Date&$expand = Person", //expand list fields

    #"Added Custom" = 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(#"Added Custom", "Items", {"value"}, {"value"}),

    #"Expanded value" = Table.ExpandListColumn(#"Expanded Items", "value"),

    #"Expanded value1" = Table.ExpandRecordColumn(#"Expanded value", "value", {"Id", "Title", "Person", "Date"}, {"Id", "Title", "Person", "Date"})

in

    #"Expanded value1"

Regards,

Pat