Why am I getting an error when trying to use 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://thebravanesesocietycouk-my.sharepoint.com/personal/jeilani_thebravanesesociety_co_uk/Lists/Customers?env=WebViewList

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"
    

I watched the tutorial he gave on how to do it (which is in the later part of the video):

https://www.youtube.com/watch?v=rsCB3kJrSkQ&ab_channel=HoosierBI

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

I used the adjusted the query into as follows:

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

After I run the query, Power BI asks me to put in my credentials but when I enter my credentials which are correct it keeps saying:

"We couldn't authenticate with the credentials provided. Please try again"

It may be because I did not rewrite the query correctly.

Any help would be greatly appreciated.

[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"

[AlexisOlson]

A problem I see frequently is people trying to connect to SharePoint using Windows credentials rather than Microsoft Account credentials. Be sure to use the latter.