Forum Discussion

aplasschaert's avatar
aplasschaert
Regular Visitor
9 years ago

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

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.

Need Help

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

    • kcantor's avatar
      kcantor
      Community Champion
      9 years ago

      aplasschaert

      To build on what Anonymous 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.

       

    • j_bujnowski's avatar
      j_bujnowski
      Regular Visitor
      9 years ago

      I have the same problem.

       

      My file content (only single column):

       

      Value
      12989
      -52890
      993
      -6101

       

      Field "Value" was recognised as text.

  • aplasschaert's avatar
    aplasschaert
    Regular Visitor
    9 years ago

    Anonymous 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        j_bujnowski
        Regular Visitor
        9 years ago

        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.