Forum Discussion
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.
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).
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.
One thing I forgot to mention. I can only have access to this data if the source is connected to my professional account. If I change it to anonumous, it gives connection error.
Thank you. When and if I find a solution, I will add more info here, if it can help someone.
First off, thank you everybody for your help.
I finally have pretty much everything working. Here's what I did, if anyone needs this help later on.
1. I used the solution that Jai-Rathinavel proposed in the Youtube video (with the tweak proposed later on) for only the columns that contain numbers, since the over 1000 numbers were giving me problems.
2. I used the V2.0 Sharepoint list connector to get the user information (and everything else that was needed).
3. I have to use the V1.0 Sharepoint list connector to get the attachment links (they are the receipts related to the other data).
If I could find a way to get the attachments links another way, it would speed up the refresh, but it's already much quicker.
The other problem I am getting, which is a bigger problem overall, is that the automatic refresh isn't working because I have a dynamic source (the solution in #1). I can manually refresh it from the desktop, but it doesn't seem to work in the PowerBI service, which is where it's needed more.
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 JoelDucharme
Thank you for the update. As the People lookup columns continue to return only numeric IDs and you are limited to accessing the data through your professional account, this might be linked to how the connector manages identity resolution or permissions in your environment. Unfortunately, these issues often involve tenant-level settings or backend behavior that we cannot fully troubleshoot here.
I suggest opening a Microsoft support ticket so the support team can investigate this directly with access to your environment. You can create a support ticket using the link below:
Create a support ticket – Power BIIf 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.