Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have the following M query to obtain data from a SharePoint site.
let
siteurl = "https://thebravanesesocietycouk-my.sharepoint.com/personal/jeilani_thebravanesesociety_co_uk/",
listname = "Customers",
itemcount = Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/",
Headers = [Accept = "application/json"]
]
)
)[value]{0}[ID],
// itemcount = Json.Document(
// Web.Contents(
// siteurl,
// [
// RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/ItemCount",
// Headers = [Accept = "application/json"]
// ]
// )
// )[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount / 5000), 5000),
skiplisttable = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumns = Table.RenameColumns(skiplisttable, {{"Column1", "Skip"}}),
ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Skip", type text}}),
//Common in only one of the fieldselect lines below, defining your select and expand columns if needed
fieldselect = "&$top=5000", // all fields with no expansion
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn,ChoiceColumn,LookupColumn", // list desired fields (no expansion) -No Spaces!
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn/LastName,PersonColumn/FirstName,ChoiceColumn,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus&$expand=PersonColumn,LookupColumn", //expand list fields - No Spaces!
GetData = Table.AddColumn(
ChangedType,
"Customers",
each
Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('"& listname & "')/" & [Skip] & fieldselect,
Headers = [Accept = "application/json"]
]
)
)
),
ExpandRecordsFromList = Table.ExpandRecordColumn(GetData, "Items", {"value"}, {"value"}),
ExpandedValue = Table.ExpandListColumn(ExpandRecordsFromList, "value"),
RemovedOtherColumns = Table.SelectColumns(ExpandedValue, {"value"})
in
RemovedOtherColumns
I am getting the following errors:
Expression.Error: The field 'value' of the record wasn't found.
Details:
odata.metadata=https://thebravanesesocietycouk-my.sharepoint.com/personal/jeilani_thebravanesesociety_co_uk/_api/$metadata*SP.ApiData.Lists/@Element
odata.type=SP.List
odata.id=https://urldefense.com/v3/__https://thebravanesesocietycouk-my.sharepoint.com/personal/jeilani_thebravanesesociety_co_uk/_api/Web/Lists(guid'c0d1dc68-fd5a-48ff-85fb-5f28cbfea88b')
odata.etag="23"
odata.editLink=Web/Lists(guid'c0d1dc68-fd5a-48ff-85fb-5f28cbfea88b')
AllowContentTypes=TRUE
BaseTemplate=100
BaseType=0
ContentTypesEnabled=FALSE
CrawlNonDefaultViews=FALSE
Created=2022-09-23T22:00:43Z
CurrentChangeToken=
StringValue=1;3;c0d1dc68-fd5a-48ff-85fb-5f28cbfea88b;638010147236830000;10497934
DefaultContentApprovalWorkflowId=00000000-0000-0000-0000-000000000000
DefaultItemOpenUseListSetting=FALSE
Description=
Direction=none
DisableCommenting=FALSE
DisableGridEditing=FALSE
DocumentTemplateUrl=
DraftVersionVisibility=0
EnableAttachments=TRUE
EnableFolderCreation=FALSE
EnableMinorVersions=FALSE
EnableModeration=FALSE
EnableRequestSignOff=TRUE
EnableVersioning=TRUE
EntityTypeName=CustomersList
ExemptFromBlockDownloadOfNonViewableFiles=FALSE
FileSavePostProcessingEnabled=FALSE
ForceCheckout=FALSE
HasExternalDataSource=FALSE
Hidden=FALSE
Id=c0d1dc68-fd5a-48ff-85fb-5f28cbfea88b
ImagePath=
DecodedUrl=/_layouts/15/images/itgen.gif?rev=47
ImageUrl=/_layouts/15/images/itgen.gif?rev=47
DefaultSensitivityLabelForLibrary=
IrmEnabled=FALSE
IrmExpire=FALSE
IrmReject=FALSE
IsApplicationList=FALSE
IsCatalog=FALSE
IsPrivate=FALSE
ItemCount=50
LastItemDeletedDate=2022-09-26T11:04:01Z
LastItemModifiedDate=2022-10-06T08:40:36Z
LastItemUserModifiedDate=2022-10-06T08:40:36Z
ListExperienceOptions=0
ListItemEntityTypeFullName=SP.Data.CustomersListItem
MajorVersionLimit=50
MajorWithMinorVersionsLimit=0
MultipleDataList=FALSE
NoCrawl=FALSE
ParentWebPath=
DecodedUrl=/personal/jeilani_thebravanesesociety_co_uk
ParentWebUrl=/personal/jeilani_thebravanesesociety_co_uk
ParserDisabled=FALSE
ServerTemplateCanCreateFolders=TRUE
TemplateFeatureId=00bfea71-de22-43b2-a848-c05709900100
Title=Customers
Using the same script, can someone please help me fix this error?.
Just a note, I got this script from here:
Here is a list of the column names:
Thanks in advance.
Solved! Go to Solution.
I tried the same code at work. It worked, I'm not sure why I was getting errors when I tried on my personal machine. Here is the full code:
let
siteurl = "https://companyname-my.sharepoint.com/personal/hamid_bee_companyname_com/",
listname = "Customers",
itemcount = Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$select=ID&$orderby=ID%20desc&$top=1",
Headers = [Accept = "application/json"]
]
)
)[value]{0}[ID],
// itemcount = Json.Document(
// Web.Contents(
// siteurl,
// [
// RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/ItemCount",
// Headers = [Accept = "application/json"]
// ]
// )
// )[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount / 5000), 5000),
skiplisttable = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumns = Table.RenameColumns(skiplisttable, {{"Column1", "Skip"}}),
ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Skip", type text}}),
//Common in only one of the fieldselect lines below, defining your select and expand columns if needed
fieldselect = "&$top=5000", // all fields with no expansion
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn,ChoiceColumn,LookupColumn", // list desired fields (no expansion) -No Spaces!
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn/LastName,PersonColumn/FirstName,ChoiceColumn,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus&$expand=PersonColumn,LookupColumn", //expand list fields - No Spaces!
GetData = Table.AddColumn(
ChangedType,
"Items",
each
Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('"& listname & "')/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect,
Headers = [Accept = "application/json"]
]
)
)
),
ExpandRecordsFromList = Table.ExpandRecordColumn(GetData, "Items", {"value"}, {"value"}),
ExpandedValue = Table.ExpandListColumn(ExpandRecordsFromList, "value"),
RemovedOtherColumns = Table.SelectColumns(ExpandedValue, {"value"}),
#"Expanded value" = Table.ExpandRecordColumn(RemovedOtherColumns, "value", {"odata.type", "odata.id", "odata.etag", "odata.editLink", "FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ID", "ContentTypeId", "Title", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "ComplianceAssetId", "field_1", "field_2", "field_3", "field_4", "field_5", "field_6", "field_7", "field_8"}, {"value.odata.type", "value.odata.id", "value.odata.etag", "value.odata.editLink", "value.FileSystemObjectType", "value.Id.1", "value.ServerRedirectedEmbedUri", "value.ServerRedirectedEmbedUrl", "value.ID", "value.ContentTypeId", "value.Title", "value.Modified", "value.Created", "value.AuthorId", "value.EditorId", "value.OData__UIVersionString", "value.Attachments", "value.GUID", "value.ComplianceAssetId", "value.field_1", "value.field_2", "value.field_3", "value.field_4", "value.field_5", "value.field_6", "value.field_7", "value.field_8"})
in
#"Expanded value"
Just a note, I had to mask some of the data.
Hi @HamidBee - could you please try the following because I am not sure the result has a [Value] column
let
siteurl = "https://thebravanesesocietycouk-my.sharepoint.com/personal/jeilani_thebravanesesociety_co_uk/",
listname = "Customers",
itemcount = Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/",
Headers = [Accept = "application/json"]
]
)
)
in
itemcount
Is it also worth checking your result is JSON Document with the following:
let
siteurl = "https://thebravanesesocietycouk-my.sharepoint.com/personal/jeilani_thebravanesesociety_co_uk/",
listname = "Customers",
temp = Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/",
Headers = [Accept = "application/json"]
]
)
),
Text = Text.FromBinary( temp )
in
Text
The first query returns:
The second query returns:
The issue with the first query is that it does not return the table with its values. Also is there a reason why you removed the 'Get data' part of the script?.
Hi @HamidBee - As I expected the results of the Web.Contents call does not include a column called [Value] hence you are getting an error. I removed all the Get Data steps because these are not excuted first by Power Query. This step is relying on the [Skip] column that is derived from the earlier itemcount step.
Looking back at @mahoneypat suggestion on the previous ticket, I think you are missing an important part of the URL call. He has added "/items" or "/ItemCount to the URL relative path.
- try these to see if the Value of the item count is available from both options:
let
siteurl = "https://thebravanesesocietycouk-my.sharepoint.com/personal/jeilani_thebravanesesociety_co_uk/",
listname = "Customers",
itemcount = Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items",
Headers = [Accept = "application/json"]
]
)
),
alternative = Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/ItemCount",
Headers = [Accept = "application/json"]
]
)
)
in
alternative
I tried the same code at work. It worked, I'm not sure why I was getting errors when I tried on my personal machine. Here is the full code:
let
siteurl = "https://companyname-my.sharepoint.com/personal/hamid_bee_companyname_com/",
listname = "Customers",
itemcount = Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/items?$select=ID&$orderby=ID%20desc&$top=1",
Headers = [Accept = "application/json"]
]
)
)[value]{0}[ID],
// itemcount = Json.Document(
// Web.Contents(
// siteurl,
// [
// RelativePath = "_api/web/lists/GetByTitle('" & listname & "')/ItemCount",
// Headers = [Accept = "application/json"]
// ]
// )
// )[value],
skiplist = List.Numbers(0, Number.RoundUp(itemcount / 5000), 5000),
skiplisttable = Table.FromList(skiplist, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
RenamedColumns = Table.RenameColumns(skiplisttable, {{"Column1", "Skip"}}),
ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Skip", type text}}),
//Common in only one of the fieldselect lines below, defining your select and expand columns if needed
fieldselect = "&$top=5000", // all fields with no expansion
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn,ChoiceColumn,LookupColumn", // list desired fields (no expansion) -No Spaces!
//fieldselect = "&$top=5000&$select=ID,Title,Date,PersonColumn/LastName,PersonColumn/FirstName,ChoiceColumn,LookupColumn/Title,LookupColumn/Project,LookupColumn/ProjectStatus&$expand=PersonColumn,LookupColumn", //expand list fields - No Spaces!
GetData = Table.AddColumn(
ChangedType,
"Items",
each
Json.Document(
Web.Contents(
siteurl,
[
RelativePath = "_api/web/lists/GetByTitle('"& listname & "')/items?$skipToken=Paged=TRUE%26p_ID=" & [Skip] & fieldselect,
Headers = [Accept = "application/json"]
]
)
)
),
ExpandRecordsFromList = Table.ExpandRecordColumn(GetData, "Items", {"value"}, {"value"}),
ExpandedValue = Table.ExpandListColumn(ExpandRecordsFromList, "value"),
RemovedOtherColumns = Table.SelectColumns(ExpandedValue, {"value"}),
#"Expanded value" = Table.ExpandRecordColumn(RemovedOtherColumns, "value", {"odata.type", "odata.id", "odata.etag", "odata.editLink", "FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ID", "ContentTypeId", "Title", "Modified", "Created", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "ComplianceAssetId", "field_1", "field_2", "field_3", "field_4", "field_5", "field_6", "field_7", "field_8"}, {"value.odata.type", "value.odata.id", "value.odata.etag", "value.odata.editLink", "value.FileSystemObjectType", "value.Id.1", "value.ServerRedirectedEmbedUri", "value.ServerRedirectedEmbedUrl", "value.ID", "value.ContentTypeId", "value.Title", "value.Modified", "value.Created", "value.AuthorId", "value.EditorId", "value.OData__UIVersionString", "value.Attachments", "value.GUID", "value.ComplianceAssetId", "value.field_1", "value.field_2", "value.field_3", "value.field_4", "value.field_5", "value.field_6", "value.field_7", "value.field_8"})
in
#"Expanded value"
Just a note, I had to mask some of the data.