PowerPivot decimal seperator on refresh

jroz2001

New Member
Joined
Feb 22, 2016
Messages
7
For a couple of years I am a happy PowerPivot user. However, I've got an issue I haven't experienced before:
I have a CSV file, delimited by ';', with some decimal numbers in it. In my country, we use ',' as decimal seperator.

When I import this CSV file with PowerPivot, I convert some columns from text to decimal number, and all is well. However, when I refresh this data in PowerPivot, my seperator is gone, and 1234,56 turns into 123456. Is there anyone out here who knows why this is happening?

What I already have tried/ checked:
- All settings on my pc are also set to use ',' as decimal seperator.
- Change advanced settings > Coding and country settings when importing this CSV in PowerPivot
- Create a schema.ini file
- Changing ',' to '.' in the CSV --> Works, however not the solution I want.

Workaround for now, when refreshing, first change those fields to text > refresh > change back to decimal number, but I would like to not use a crappy workaround.

Thanks in advance!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
try PowerQuery
import CSV
delimiter: ;
set column as text
Query Options - Regional Settings: your country (it should convert , to . )
set column as decimal
load to Data Model
 
Last edited:
Upvote 0
That's an option indeed, however some others should also have the possibility to update it, but they don't have PowerQuery, but do have PowerPivot. So I would like to only use PowerPivot.
 
Upvote 0
If you've PowerPivot only it means this is add-in so you can get PowerQuery add-in
if you've PowerPivot built-in it means you've PowerQuery (Get&Transform) built-in
if you want resolve this problem with PowerPivot only try to call Microsoft
 
Last edited:
Upvote 0
I've got both as add-in, but not everyone within the company has PowerQuery. Install it on every pc is of course an option, but prefer to solve it within PowerPivot. If this is not possible, so be it, but it's kinda stupid that PowerPivot can handle comma as decimal seperator when I first load an CSV, but can't handle it when refreshing, right? So that's why I posted this question, maybe I'm doing something wrong, or maybe there is an solution within PowerPivot for this.
 
Upvote 0
One of option is changing your local settings (system) because PowerPivot use it for recognize "style" of imported file (not tested - make Office compatible with System)
 
Last edited:
Upvote 0
one more

system: ,
excel: , (Options-Advanced-Editing Options-Use Sytem Separators [or set your own])
PowerPivot - Table Import Wizard - Advanced : set your country language (it doesn't work for me but maybe for you :) )

if not - I give up
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top