Forum Discussion

HamidBee's avatar
HamidBee
Icon for Power Participant rankPower Participant
3 years ago
Solved

How can I solve this error I'm getting with my M query?

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:

 

https://community.powerbi.com/t5/Desktop/quot-the-following-data-sources-currently-don-t-support-refresh/m-p/2831117#M977286

 

Here is a list of the column names:

 

Thanks in advance.

 

Need Help
Show and Tell
Tips and Tricks
  • HamidBee's avatar
    HamidBee
    3 years ago

    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. 

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

     

     

     

    • HamidBee's avatar
      HamidBee
      Icon for Power Participant rankPower Participant
      3 years ago

      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?. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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