Sharepoint list import using Implementation 2.0 gives error for a number over 1000.

Hi JoelDucharme,

Thanks again for providing the detailed code and context you’re making great progress!

I noticed a likely issue with the line that expands a lookup column.

Your original line:

fieldselect = "&$top=5000&$select = Id,fournisseur,approbation_superviseurId/Titre&$expand = People"

is likely causing problems because:

• "People" isn't a valid field in your list.
• The correct field name to expand is usually the same as the lookup column (e.g., approbation_superviseurId), and SharePoint expects "Title" (not "Titre") in the $select even if the list is in French.

I've provided a corrected version of the full M code above with this fix applied. Please give it a try and let us know how it works out for you.

M Code:

let
    sitename = "finances-applications",
    listname = "demande_paiement",
    baseurl = "https://lescompagnonsdesfrancslo871.sharepoint.com/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}}),

    // ✅ Fixed select and expand clause
    fieldselect = "&$top=5000&$select=Id,fournisseur,approbation_superviseurId/Title&$expand=approbation_superviseurId",

    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"),
    #"value développé" = Table.ExpandRecordColumn(#"Expanded value", "value", {
        "Id", "fournisseur", "facture_date", "facture_numero", "facture_details", "AuthorId", "EditorId", 
        "Created", "Modified", "approbateur_superviseurId", "approbateur_cadreId", "l1_projet", "l1_no_compte", 
        "l1_activite", "l1_subvention", "l1_total", "l1_taxes_1", "total_grand", "total_taxes_1", "total_taxes_2", 
        "approbation_sup_statut", "approbation_sup_date", "approbation_sup_notes", "approbation_cadre_statut", 
        "approbation_cadre_date", "approbation_cadre_utilisateurId", "approbation_cadre_notes", 
        "approbation_sup_utilisateurId", "approbation_tresorie_statut", "approbation_tresorie_date", 
        "approbation_tresorie_utilisateurId", "approbation_tresorie_notes", "approbation_president_statut", 
        "approbation_president_date", "approbation_president_utilisateuId", "approbation_president_notes", 
        "finances_methode_paiement", "finances_numero_methode_paiement", "finances_no_fichier_tfe", 
        "finances_notes", "finances_verification_statut", "finances_verification_date", 
        "finances_verification_utilisateuId", "finances_inscription_statut", "finances_inscription_date", 
        "finances_inscription_utilisateurId", "statut", "est_urgent", "type_demande", "membre_du_personnelId", 
        "carte_credit", "magasin_vendeur", "est_devise_etrangere", "finances_non_approuve", 
        "finances_non_approuve_note", "est_retire", "annee_fiscale", "Attachments"
    }),

    #"Type modifié" = Table.TransformColumnTypes(#"value développé",{
        {"facture_date", type datetime}, 
        {"Modified", type datetime}, 
        {"approbation_sup_date", type datetime}, 
        {"approbation_cadre_date", type datetime}, 
        {"approbation_tresorie_date", type datetime}, 
        {"approbation_president_date", type datetime}, 
        {"finances_verification_date", type datetime}, 
        {"finances_inscription_date", type datetime}
    })
in
    #"Type modifié"

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.