Forum Discussion

JoelDucharme's avatar
JoelDucharme
Helper I
1 year ago
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...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    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.

JoelDucharme's avatar
JoelDucharme
Helper I
1 year ago

Thanks. This seems to work... almost.

 

I'm trying the web connector as shown in the video. But I have 2 problems.

 

  1. If I use the "all fields with no expansion" option, the connection works, but obviously, my people collumns aren't expanded. I only get a number in those fields. The other two options are giving me connection issues where it doesn't recognise my credentials. It's just telling me that I can't authenticate with the given credentials. All this in the PowerBI desktop.
  2. If I use the first option to get connected, the semantic model cannot refresh. It's saying that Dynamic Data sources cannot be refreshed in PowerBI service. If this means that the data is always up to date, it's even better, but if the data isn't refreshed, than, it's obviously a problem. 😛 

Any ideas?

JoelDucharme's avatar
JoelDucharme
Helper I
1 year ago

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?

  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    Super User
    1 year ago

    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.

     

    • JoelDucharme's avatar
      JoelDucharme
      Helper I
      1 year ago

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

      • v-kpoloju-msft's avatar
        v-kpoloju-msft
        Community Support
        1 year ago

        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.