Forum Discussion
Solved
Sharepoint list import using Implementation 2.0 gives error for a number over 1000.
If I connect the list with version 1.0, it works, but it's really slow. So I'm trying to convert my Powerquery using version 2.0, but, in 3 columns, that are a "personalised" type in the list, number...
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.
Hi Jai-Rathinavel ,
Your solution is almost working. I have been able to get access to the list and almost all it's information. I cannot get the persons' names to show, only a number, and only when I use the first "fieldselect" option. The other two options don't work for me at all. They just give me connexion errors.
I think that I have to connect with Web API to get those but it's not working. I created an API Key and when I use it to connect, it gives me an error saying "A web API key can only be specified when a web API key name is provided".
Any ideas how I can fix this?
Hi JoelDucharme Can you copy paste the M code so that we can see which API you are connecting to, I will try to replicate it from my end.
Thanks. Sorry for the slow response. I am working at this only a few hours a day, in the morning, and I have no training on this. I'm trying to wrap my head around this. Here is the M code:
let
sitename ="finances-applications", // if a subsite use "Site/SubSite"
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}}),
//fieldselect = "&$top=5000", // all fields with no expansion
//fieldselect = "&$top=5000&$select = Id, Title, 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, l2_projet, l2_no_compte, l2_activite, l2_subvention, l2_total, l2_taxes_1, l3_projet, l3_no_compte, l3_activite, l3_subvention, l3_total, l3_taxes_1, l4_projet, l4_no_compte, l4_activite, l4_subvention, l4_total, l4_taxes_1, est_urgent, l5_projet, l6_projet, l7_projet, l8_projet, l9_projet, l10_projet, l5_no_compte, l6_no_compte, l7_no_compte, l8_no_compte, l9_no_compte, l10_no_compte, l5_activite, l6_activite, l7_activite, l8_activite, l9_activite, l10_activite, l5_subvention, l6_subvention, l7_subvention, l8_subvention, l10_subvention, l5_total, l6_total, l7_total, l8_total, l9_total, l9_subvention, l10_total, l5_taxes_1, l6_taxes_1, l7_taxes_1, l8_taxes_1, l9_taxes_1, l10_taxes_1, membre_du_personnelId, carte_credit, magasin_vendeur, est_devise_etrangere, finances_non_approuve, finances_non_approuve_note, est_retire, annee_fiscale, Attachments", // list desired fields (no expansion)
fieldselect = "&$top=5000&$select = Id,fournisseur,approbation_superviseurId/Titre&$expand = People", //expand list fields,
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_superviseurStringId", "approbateur_cadreId", "approbateur_cadreStringId", "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_utilisateurStringId", "approbation_cadre_notes", "approbation_sup_utilisateurId", "approbation_sup_utilisateurStringId", "approbation_tresorie_statut", "approbation_tresorie_date", "approbation_tresorie_utilisateurId", "approbation_tresorie_utilisateurStringId", "approbation_tresorie_notes", "approbation_president_statut", "approbation_president_date", "approbation_president_utilisateuId", "approbation_president_utilisateuStringId", "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_verification_utilisateuStringId", "finances_inscription_statut", "finances_inscription_date", "finances_inscription_utilisateurId", "finances_inscription_utilisateurStringId", "statut", "l2_projet", "l2_no_compte", "l2_activite", "l2_subvention", "l2_total", "l2_taxes_1", "l3_projet", "l3_no_compte", "l3_activite", "l3_subvention", "l3_total", "l3_taxes_1", "l4_projet", "l4_no_compte", "l4_activite", "l4_subvention", "l4_total", "l4_taxes_1", "fournisseur_est_nouveau", "fournisseur_nouveau", "est_urgent", "l5_projet", "l6_projet", "l7_projet", "l8_projet", "l9_projet", "l10_projet", "l5_no_compte", "l6_no_compte", "l7_no_compte", "l8_no_compte", "l9_no_compte", "l10_no_compte", "l5_activite", "l6_activite", "l7_activite", "l8_activite", "l9_activite", "l10_activite", "l5_subvention", "l6_subvention", "l7_subvention", "l8_subvention", "l10_subvention", "l5_total", "l6_total", "l7_total", "l8_total", "l9_total", "l9_subvention", "l10_total", "l5_taxes_1", "l6_taxes_1", "l7_taxes_1", "l8_taxes_1", "l9_taxes_1", "l10_taxes_1", "type_demande", "membre_du_personnelId", "membre_du_personnelStringId", "carte_credit", "magasin_vendeur", "est_devise_etrangere", "finances_non_approuve", "finances_non_approuve_note", "est_retire", "annee_fiscale", "Attachments"}, {"Id", "fournisseur", "facture_date", "facture_numero", "facture_details", "AuthorId", "EditorId", "Created", "Modified", "approbateur_superviseurId", "approbateur_superviseurStringId", "approbateur_cadreId", "approbateur_cadreStringId", "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_utilisateurStringId", "approbation_cadre_notes", "approbation_sup_utilisateurId", "approbation_sup_utilisateurStringId", "approbation_tresorie_statut", "approbation_tresorie_date", "approbation_tresorie_utilisateurId", "approbation_tresorie_utilisateurStringId", "approbation_tresorie_notes", "approbation_president_statut", "approbation_president_date", "approbation_president_utilisateuId", "approbation_president_utilisateuStringId", "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_verification_utilisateuStringId", "finances_inscription_statut", "finances_inscription_date", "finances_inscription_utilisateurId", "finances_inscription_utilisateurStringId", "statut", "l2_projet", "l2_no_compte", "l2_activite", "l2_subvention", "l2_total", "l2_taxes_1", "l3_projet", "l3_no_compte", "l3_activite", "l3_subvention", "l3_total", "l3_taxes_1", "l4_projet", "l4_no_compte", "l4_activite", "l4_subvention", "l4_total", "l4_taxes_1", "fournisseur_est_nouveau", "fournisseur_nouveau", "est_urgent", "l5_projet", "l6_projet", "l7_projet", "l8_projet", "l9_projet", "l10_projet", "l5_no_compte", "l6_no_compte", "l7_no_compte", "l8_no_compte", "l9_no_compte", "l10_no_compte", "l5_activite", "l6_activite", "l7_activite", "l8_activite", "l9_activite", "l10_activite", "l5_subvention", "l6_subvention", "l7_subvention", "l8_subvention", "l10_subvention", "l5_total", "l6_total", "l7_total", "l8_total", "l9_total", "l9_subvention", "l10_total", "l5_taxes_1", "l6_taxes_1", "l7_taxes_1", "l8_taxes_1", "l9_taxes_1", "l10_taxes_1", "type_demande", "membre_du_personnelId", "membre_du_personnelStringId", "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é"(note... My list in in French, if it makes a difference).
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.Thank you so much for helping me with this. Unfortunately, it's always the same thing. The output for the people lookup columns are all just a number. Different numbers for different users.