How can I adjust Hoosier's M query so that it retrieves my SharePoint list data?

[HamidBee]

I am trying to retrive data from my SharePoint lists:

Here is the link to my SharePoint list called 'Customers':

https://companyname-my.sharepoint.com/personal/hamid_bee/Lists/Customers/AllItems.aspx

Just a note, I had to mask some sensitive data. I was looking for a quick way to retrieve data from the SharePoint list which is what lead me to find Hoosier's M query for his SharePoint site:

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"

My problem is I can't seem to adjust this M query for my SharePoint list without getting a host of errors. Any help would be greatly appreciated. 

[ppm1]

@HamidBee  FYI that I recently updated this query so that it can be used with Scheduled Refresh in the service, and to fix and issue with duplicate values (if some items previously deleted). Please see this article for the two new versions. Any feedback is welcomed.

Updated – Get SharePoint List Data … Fast – Hoosier BI

Pat

[prathyoo]

I tried this and after publishing the report, the refresh failed with the error - 

This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

Now, after further research I found the blog from Chris Webb - Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI Chris Webb'...

Using that, I changed the step Custom1 to use query parameters as follows -

Custom1 = Table.AddColumn(#"Changed Type", "Items", each Json.Document(Web.Contents(baseurl& "/items", [Headers=[Accept="application/json"],Query=[#"$skipToken"="Paged=TRUE",p_ID=[Skip],#"$top"="5000"]]))),

With this change the refresh error was also resolved.

[v-yanjiang-msft]

Hi @HamidBee ,

When connect to SharePoint list in Power BI, it asks for root URL that not including sub folders.

Try to change you URL to:

https://companyname-my.sharepoint.com/

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HamidBee · Accepted solution]

The problem was the M query. I adjusted the query and entered the same credentials as I tried to enter above and I was able to successfully query the list. Here is the M query that I used:

let
sitename ="jeilani_thebravanesesociety_co_uk", // if a subsite use "Site/SubSite"
listname = "Customers",
baseurl = "https://thebravanesesocietycouk-my.sharepoint.com/personal/" & 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"