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

[JoelDucharme]

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, numbers of value over 3000 (which is formatted 3 000,00 - fr-CA) gives me an error. The screenshot is in French. It says. "Sorry... We cannot proceed to convert in a number." 

Unfoturnately, I cannot change the column type since it's being fed by a PowerApp that needs it to be formatted this way. 

I tried to change the regional settings to fr-CA. I tried to change the column type in PowerQuery with using the regional function. I tried English US, International, Canada.... nothing works. Always the same error. 

I even tried to convert it to text, even if it would have been a useless column. Even that didn't work.

Any ideas for what I can do to make it work?

[v-kpoloju-msft]

Hi @JoelDucharme,
Thank you for reaching out to the Microsoft fabric community forum. Also @Jai-Rathinavel, for his inputs on this thread. I have identified few workarounds that may help resolve the issue.

Thank you for providing the detailed context and troubleshooting steps you've already taken. This information is very helpful in understanding the situation.

From your description, it appears the issue is with how the SharePoint Online List connector (Implementation 2.0) parses locale-specific number formats. Specifically, numbers formatted in fr-CA (e.g., 3 000,00) use a space as the thousand’s separator and a comma as the decimal separator. The 2.0 connector currently does not handle these formats well, resulting in a DataFormat.Error when attempting to parse these values as numbers, even if the column is later converted or treated as text.

To continue using the faster 2.0 connector while avoiding conversion errors, you can manually clean and convert these values in Power Query using a custom transformation. Please follow these steps:

• In Power Query, identify the affected column(s).
• Create a custom column using this transformation:

let

    CleanedText = Text.Replace(Text.Replace([YourColumnName], " ", ""), " ", ""),

    DotDecimal = Text.Replace(CleanedText, ",", "."),

    ParsedNumber = try Number.From(DotDecimal) otherwise null

in

    ParsedNumber

This removes both regular and non-breaking spaces, replaces the comma with a dot, and then converts the value to a number in a culture-invariant format.

If multiple columns are affected, you can use Table.TransformColumns:

let

    Source = [YourSourceStepHere],

    CleanedTable = Table.TransformColumns(

        Source,

        {

            {"Column1", each try Number.From(Text.Replace(Text.Replace(Text.Replace(_, " ", ""), " ", ""), ",", ".")) otherwise null, type number},

            {"Column2", each try Number.From(Text.Replace(Text.Replace(Text.Replace(_, " ", ""), " ", ""), ",", ".")) otherwise null, type number}

        }

    )

in

    CleanedTable

Be sure to replace "Column1"/"Column2" with your actual column names.

For your reference, here are a few related Microsoft Learn articles:

• Supported languages and countries/regions for Power BI
• List of Power Query connectors
• SharePoint Online integration in Power Apps
  
  

Unfortunately, locale aware number parsing from custom SharePoint columns isn’t currently handled automatically by Implementation 2.0. This workaround allows you to preprocess and convert the values manually while still benefiting from the improved performance of the 2.0 connector.

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.

[v-kpoloju-msft]

Hi @JoelDucharme,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[JoelDucharme]

Unfortunately, my time is limited this week. I have tried Jai-Rathinavel's idea with the custom code and direct dynamic access. It's almost working. I have a few bugs to figure out, when I get the time.

[v-kpoloju-msft]

Hi @JoelDucharme,

Thank you for the update. I understand that debugging can be time consuming, especially with a busy schedule. It's good to hear that Jai-Rathinavel’s approach is almost working for you. If the response provided by the community member @Jai-Rathinavel,. If yes, kindly accept his useful reply as a solution and give him Kudos. It would be appreciated.

Thank you for your understanding!

[v-kpoloju-msft]

Hi @JoelDucharme,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Jai-Rathinavel]

@JoelDucharme Is the Implementation 1.0 is slow in power bi service or in desktop ?

[JoelDucharme]

Both. It takes over 1 hour to sometimes close to 2 hours for refresh the semantic model in power bi service. When I make a change in the desktop version, every step is so teadious.

I know that my power query model (sorry, I'm not sure of the terms...) is not very streamlined and I'm working on it, but there has to be something wrong with the way it's working. That's why I'm exploring the 2.0 implementation, since, from what I've seen in a few places, it should be much quicker. 

[Jai-Rathinavel]

@JoelDucharme Please check out this below video to make your sharepoint refresh faster and see if that helps

https://youtu.be/rsCB3kJrSkQ?si=iPnMoYrnZlvOS_eK

[JoelDucharme]

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]

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]

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]

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 · Accepted solution]

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]

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.

[JoelDucharme]

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.

[v-kpoloju-msft]

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 BI

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]

Thank you. When and if I find a solution, I will add more info here, if it can help someone.

[JoelDucharme]

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.