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/", l...
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. 

     

     

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




       

      • HamidBee's avatar
        HamidBee
        Icon for Power Participant rankPower Participant
        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.