Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
@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
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.
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?
Proud to be a Super User!
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.
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.
Proud to be a Super User!
I have the same problem.
My file content (only single column):
Value
12989
-52890
993
-6101
Field "Value" was recognised as text.
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.
Proud to be a Super User!