Forum Discussion
Space in number type when imported from Sharepoint List
Hello everyone,
While updating my dashboards this morning, a problem showed up for all of them (different PowerBI files, different dataset, same sharepoint site):
The number fields are no longer recognized for values above 999 because a space is now inserted
However, in Sharepoint List, the fields are correctly displayed and calculated as you can see on the sum.
I don't know why this space got inserted (no changes on SP list, no options to remove it). I tried in powerquery to convert the columns in text and remove the space before to convert back in number. But not working.
Anyone faced the same issue?
Thank you
I partially fixed the issue. It all started when an IT admin changed the regional settings for the sharepoint Site. Consequently, I modified the same for my PowerBI files in settings. If it now works for currency, it still can't recognize the space separator in numbers (local fr-FR). The only solution was to remove separators in the sharepoint list view.
6 Replies
- Stachu
Community Champion
can you share the M code for the step where the error values appear for the first time?
So, I've done different tries but here is the most significant one
- Installed a clean powerBI on a my personal desktop
- Imported the list with 2.0 implementation
Without further processing the error is immediate on the column of type number.
However, when I change to implementation 1.0 ; No problem it works!!!
Then I created a new list on another site with number column type and imported with 2.0 implementation and the error is there,
As this problem appeared overnight, it looks that something happened either on the config of my sites or on the implementation 2.0
The M Code;
let Source = SharePoint.Tables("xxxx.sharepoint.com/sites/dpp", [Implementation="2.0", ViewMode="All"]), #"367c959f-4cc5-43db-9c01-d7b8cca74e60" = Source{[Id="367c959f-4cc5-43db-9c01-d7b8cca74e60"]}[Items] in #"367c959f-4cc5-43db-9c01-d7b8cca74e60"The source column config;
The output;
I partially fixed the issue. It all started when an IT admin changed the regional settings for the sharepoint Site. Consequently, I modified the same for my PowerBI files in settings. If it now works for currency, it still can't recognize the space separator in numbers (local fr-FR). The only solution was to remove separators in the sharepoint list view.
- mike_honey
Memorable Member
I've just struck the same issue against a SharePoint site set to French. A viable workaround seems to be to revert to the 1.0 Implementation in the Source step. This correctly presents the numeric values (without space as a thousands separator).
Unfortunately, that takes you back to the old functionality: including all columns and mangling the column names somewhat. Both of those can be resolved fairly easily in the Query Editor (Choose Columns and Rename Columns steps).
- mike_honey
I found a better workaround for when the SharePoint site is set to French and numbers can't be read from Lists. I created a Power BI Gen1 Dataflow - that reads the List values without Errors. It saves them as numbers which I can then pull into Power BI Desktop via the Dataflows connector.
However the Power BI Gen1 Dataflow loses the detail for columns that are Table-type or List-type, e.g. SharePoint Lookup or List columns. I guess I could build 2 queries and Merge them on ID, but quite clunky to maintain.
