Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HamidBee
Impactful Individual
Impactful Individual

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

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. 

 

1 ACCEPTED SOLUTION
HamidBee
Impactful Individual
Impactful Individual

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"

 

 

View solution in original post

4 REPLIES 4
ppm1
Solution Sage
Solution Sage

@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

Microsoft Employee
prathyoo
Helper III
Helper III

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
Community Support
Community Support

Hi @HamidBee ,

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

vkalyjmsft_0-1664877620175.png

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
Impactful Individual
Impactful Individual

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"

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors