CSV data does not recognize column as numbers when contains negative numbers

[aplasschaert]

When connecting to a CSV (generated with powershell) a column with numbers is recognized by Power BI web as numbers.

Untill the column contains negative numbers then the column is recognized as string.

This results in a diffrent (unwanted) non numerical sorting in Graph's.

How to handle columns in a CSV that conains negative numbers.

[aplasschaert]

@KHorseman and @kcantor Thansk for your support.

Incorrect characters is the first thing I excemined. There are no spaces in the input and the minus is ASCII 2D when viewed with a HEX editor. I have unsuccesfulyy tried to generate a csv in unicode with the minus as U+2212 in PowerShell.

When using PowerBI desktop, every test set I have is imported correct as numbers, neg and pos combined.
But I realy want to accomplish this in PowerBI Service (Web) because I am creating an auto updated Dashboard with input from various powershell scripts. I am doing this via CSV's on Onedrive for business, which automaticaly update the dataset when the CSV is changed.

@j_bujnowski Maybe my testing can help you.
Testsets I used and their results

• only positive numbers - Column imported as numbers
• change one number to negitive - Column changed to string
• only negative numbers - Column imported as numbers
• change one number to positive- Column stays as numbers
• random mix of neg and pos - Column imported as string
• Specific mix of neg and pos, starting with 10 neg - Column imported as numbers

I believe Microsoft has a funny algortihem to interpet numbers.
This is so frustrating that I moved away from this solution and am investigating the use of PowerBI API.
This gives me more controle over the column type definition and as a bonus the possibility to directly update data from powershell and bypass Ondedrive.  With PowerBIPS in powershell via PowerBI API I created a dataset with a int64 column and have now succesfully loaded mixed neg and pos numbers.

Thies does not solve the issue here but it seems a better solution for me.

[KHorseman]

Have you actually explicitly set the column to a number datatype in the query editor before loading, or are you just allowing Power BI to auto-detect data types?

[j_bujnowski]

Power BI Service automaticaly recognized data types.

I'm using PowerBI Service (Web) to create auto updated Dashboard. I am using CSV's on the Onedrive.

When CSV is changed datasets/reports/dashboards are automatically refreshed.

In case Power BI Desktop everything works correctly with the same data.

[KHorseman]

Are you sure the negative numbers are actually marked with a true minus - character and not the dash – character? If even one of them is the dash character it will read as text. Also check to make sure there aren't space characters.

[j_bujnowski]

I have the same problem.

My file content (only single column):

Value
12989
-52890
993
-6101

Field "Value" was recognised as text.

[kcantor]

@aplasschaert

To build on what @KHorseman stated. In your query editor use the replace text function to replace all hyphen/dash with a minus sign and then replace all spaces with no selection being made (to remove spaces). I generally do a transform as well with trim and clean in case there are other issues present.