Importing a SharePoint Online list into Power BI with currency in regional format

[paoloutile]

I have a SharePoint Online list with columns containing decimal numbers (representing currency) which are in the Canadian French locale. I’m trying to import this data into Power BI, and it seems that Power BI automatically detects the field type in the list. It therefore expects decimal numbers, but immediately generates errors: since the Canadian French format adds a space at the thousands (e.g. 3 000$), the numbers imported from the list are not compatible with the decimal format imposed by Power BI.

I say "imposed" because, in the applied steps and the M code, there is no step where the columns are explicitly converted to decimal numbers. This prevents me from using Table.TransformColumnTypes with the fr-CA parameter since the values are in error. I don't see any parameters in SharePoint.Tables that allow me to fix this. Is there a way to fix this?

My code:

let
    Source = SharePoint.Tables("https://my-tenant.sharepoint.com/sites/my-site", [Implementation="2.0", ViewMode="All"]),
    #"id" = Source{[Id="id"]}[Items]
in
    #"id"

The result of this code is a set of columns which already have a type assigned to them and a consequent set of errors on decimal number fields with values such as 3 000$. 

[johnbasha33 · Accepted solution]

Hi @paoloutile 

If Implementation=null fails, stop using SharePoint.Tables for that list

In the same March 2026 thread, the fallback recommendation was to pull the list through OData / REST instead, so you control the conversion yourself before Power Query forces the type.

If Amount still arrives as text like 3 000$, clean and parse it explicitly:

let
    Source =
        OData.Feed(
            "https://my-tenant.sharepoint.com/sites/my-site/_api/web/lists/getbytitle('MyList')/items?$select=Id,Title,Amount",
            null,
            [Implementation = "2.0"]
        ),

    Cleaned =
        Table.TransformColumns(
            Source,
            {
                {
                    "Amount",
                    each
                        if _ = null then
                            null
                        else
                            Number.FromText(
                                Text.Replace(
                                    Text.Replace(
                                        Text.Replace(Text.From(_), "$", ""),
                                        Character.FromNumber(160),
                                        ""
                                    ),
                                    " ",
                                    ""
                                ),
                                "fr-CA"
                            ),
                    type number
                }
            }
        )
in
    Cleaned

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[v-tejrama]

Hi @paoloutile ,

Thank you @BeaBF  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[BeaBF]

@paoloutile Hi!

I would try this first:

let
Source = SharePoint.Tables(
"https://my-tenant.sharepoint.com/sites/my-site",
[Implementation=null]
),
#"id" = Source{[Id="id"]}[Items]
in
#"id"

If that still errors, I’d stop fighting SharePoint.Tables and switch to an OData-based pull of the list, then explicitly convert the currency columns using fr-CA. 

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[paoloutile]

Hi @BeaBF, I attempted implementation=null and it returns an empty list. I will attempt an OData method instead. Thanks!