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