Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

[Data Format Error] We couldn't convert to Number

  • 1.  [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 13 days ago
    Hello,
    I'm trying to do the exercise of  Manuel LORENZ: Population-Country
    But for two days I'm struggling with this error message: [Data Format Error] We couldn't convert to Number
    It seems that this error comes from the field Year which is so simple...
    What can I do to solve this issue ?
    Thanks for your help

    Gérard


    ------------------------------
    GD61
    ------------------------------


  • 2.  RE: [Data Format Error] We couldn't convert to Number

    Posted 13 days ago
    Gerard -- it is possible that in your data for column "year" there a row(s) that are not in number format.

    Take a look at the raw file and I bet something will pop out.

    ------------------------------
    John Henning

    Houston
    ------------------------------



  • 3.  RE: [Data Format Error] We couldn't convert to Number

    Top Contributor
    Posted 13 days ago
    Hi @Gérard Ducouret

    I agree with @John Henning, that there might be some other values in your column.

    Also, I can see that your year column already says "123" so its probably converted to number.
    Another thing is that if your dataset is running on Direct Query, then you are not allowed to make any changes and they have to be configured at the source itself.

    Please let me know if I am missing anything here.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 4.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 13 days ago
    Hi John and Vishesh,

    It seems that this error appears only in the third query. I checked in the original csv file: nothing abnormal excepted some N/A values and some blank values that I already deleted in the Query Editor.
    I tried to copy/paste this table into Excel but I got only the first thousand records while the number of records is greater than 215 000.
    What could I try now ?

    Thanks for your help

    ------------------------------
    GD61
    ------------------------------



  • 5.  RE: [Data Format Error] We couldn't convert to Number

    Silver Contributor
    Posted 13 days ago
    Is it possible you have some "blank" rows at the bottom of the file. Or subtotals or some kind of footer?

    Try to open you file in Excel and press Ctrl-End to jump down to the very bottom. This will show you whether you have any garbage lurking there. Don't copy and paste. You should be able to just open the file in Excel.

    Once you get it in Excel, try the AutoFilter there to look for bad values. You also can sort in both ascending and descending order to bring "bad" values to the top.

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 6.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 13 days ago
    Thanks Christopher,

    How can I open a pbix file with Excel ?

    Thanks again

    Gérard

    ------------------------------
    GD61
    ------------------------------



  • 7.  RE: [Data Format Error] We couldn't convert to Number

    Posted 12 days ago
    To find the source of the error(s), in the Query Editor, add a Keep Errors step immediately after the problematic step (or at the end, if no error shows up in the preview). Keep Errors can be found in the drop-down to the left of the first column header in the preview pane. This will filter your dataset to just rows with errors. In the column in question, click on the blank space in cell labeled "Error" (but NOT the word Error itself), and you'll see the details below the preview grid.

    You may want to check several errors to get a sense of the different issues your query might need to handle. When you're done investigating, remove the Keep Errors step.

    ------------------------------
    Michael Taubman
    Senior Consultant
    9176703945
    ------------------------------



  • 8.  RE: [Data Format Error] We couldn't convert to Number

    Top Contributor
    Posted 12 days ago
    Hi ​@Gérard Ducouret​,

    If all the above suggestions don't solve your problem, then please can you share your sample file with us.

    It will help us resolve the issue faster.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------



  • 9.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 12 days ago
      |   view attached
    Hi Vishesh,

    Please find herewith attached file: GD Population exo 01.pbix  

    Thanks

    ------------------------------
    GD61
    ------------------------------

    Attachment(s)



  • 10.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 12 days ago
    Michael,

    The only thing I get with "Keep Errors" is a yellow message: "DataFormat Error"
    What did I miss?
    Thanks

    ------------------------------
    GD61
    ------------------------------



  • 11.  RE: [Data Format Error] We couldn't convert to Number

    Posted 12 days ago
    Edited by Michael Taubman 12 days ago
    It's telling you the value of the field is "#N/A".  You've said you are removing "N/A" values in your query.  Are you also removing "#N/A"?

    ------------------------------
    Michael Taubman

    ------------------------------



  • 12.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 12 days ago
    Yes, I tried to remove #N/A, but with no avail. :-(​

    ------------------------------
    GD61
    ------------------------------



  • 13.  RE: [Data Format Error] We couldn't convert to Number

    Silver Contributor
    Posted 12 days ago
    Hi, Gerard:

    My apologies. My suggestion was to open the CSV file in Excel to make sure you don't have any bad rows or non-numerical values, as John suggested above.

    Here's another idea that might work: You could just create a table visual, drag your year column to it and sort in both ascending and descending orders to look for blanks and other non-numeric values.​​​

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------



  • 14.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 12 days ago
    Hi Christopher,
    Create a table visual:
    I cannot do that because when I click on Apply I get the error:  [DataFormat.Error] We couldn't convert to Number

    ------------------------------
    GD61
    ------------------------------



  • 15.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 12 days ago
    The full error message is:

    Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataFormat.Error] We couldn't convert to Number.. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. '.
    What are OLE DB or ODBC errors ?

    Thanks

    ------------------------------
    GD61
    ------------------------------



  • 16.  RE: [Data Format Error] We couldn't convert to Number

    Top Contributor
    Posted 11 days ago
      |   view attached
    Hi @Gérard Ducouret​​,

    So I was able to isolate the error to your '2050-2100' file and it contained #N/A values as suggested by @Michael Taubman.



    After trying to remove the #N/A from within power query, I was unable to do it, IDK why.


    So eventually I had to resort to modifying your data files and removing the errors there. Even there, for reasons unknown when I tried to filter out the #N/A the last row somehow got hidden. So I manually modified the file and Voila!!!

    Finally I was able to load the data into the PBI model and append them.
    I have not enabled the load of individual tables in the data model, as I think you will be working on the appended table. They are there in the Query Editor if you require them.

    So please have a look, at it and let us know if this worked for you or not.

    ​​​​Hope this helps.

    Thank you,

    ------------------------------
    Vishesh Jain
    Owner
    VR Construction
    ------------------------------

    Attachment(s)

    pbix
    GD.pbix   10.24MB 1 version


  • 17.  RE: [Data Format Error] We couldn't convert to Number

    Top Contributor
    Posted 10 days ago
    Hi @Gérard Ducouret

    Have you tried in query editor ​​the M function 'Table.ReplaceErrorValues'? To access from UI right click the column header and select the 'Replace Error' option. If you type the value to replace as null, then in the returned table in power BI desktop you will get blank value, or you can replace with any value for that matter.

    ​Please see if this helps you.

    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------



  • 18.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 10 days ago
    Hi @Gopa Kumar​,

    Yes, I have tried this, but with no avail...

    Thanks

    ------------------------------
    GD61
    ------------------------------



  • 19.  RE: [Data Format Error] We couldn't convert to Number

    Top Contributor
    Posted 10 days ago
    What did you replace the errors with?

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    New Seasons Market
    Portland OR
    ------------------------------



  • 20.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 10 days ago
    Hi @Vishesh Jain,

    I thanks you very much for all the great work to solve my issue.​ I'll work on that the next days.

    Gérard


    ------------------------------
    GD61
    ------------------------------



  • 21.  RE: [Data Format Error] We couldn't convert to Number

    Posted 10 days ago
    Hi Gérard,

    In your query 'Population-country-2050-2100', the third step is 'Changed Type'.  If you look at the M code generated by this step (make sure the formula bar is visible), you'll see it's converting the 'Time' column to Int64. This is where the error occurs.  In order to handle this properly, you must replace all bad values (such as "#N/A") prior to this step.  Insert a Replace Value step before the types are changed and you should be fine.

    When debugging in the query editor, my suggestion is always to start at the very first step and work your way down, inspecting your data and looking for errors at each step.  This will help you isolate where the errors are occurring, and what you might need to do about it.

    Best,
    Mike

    ------------------------------
    Michael Taubman
    Analytics Consultant
    ------------------------------



  • 22.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 10 days ago
    Hi Mike,
    Your suggestion is very interesting: I'll work on it tomorrow!
    Thanks

    Gérard

    PS, I think that Int64 means Integer 64 bits?

    ------------------------------
    GD61
    ------------------------------



  • 23.  RE: [Data Format Error] We couldn't convert to Number

    Top Contributor
    Posted 10 days ago
    Edited by Gopa Kumar Sivadasan 10 days ago
      |   view attached
    Hi @Gérard Ducouret

    Thanks for sending the CSV files.

    PFA the corrected file. Please check. You can re-connect the csv files from your end.

    The issue, as was mentioned by others, was in the Population-country-2050-2100.csv. As shown below the error shows in 3 rows in all the columns. Since some of these columns are number types, it throws the error in power BI.


    However, you have two options to remove errors. #NA etc are errors and you should avoid replacing by the normal replace method. You should replace / remove the errors with explicit M functions for errors.

    The first option is to remove all the error rows using 'Table.RemoveRowsWithErrors'. This will remove all the rows with errors. From UI, you access it as below:



    In the pbix I have used this.

    Second is the one I had mentioned in the thread earlier viz replace errors. In this case, the replace should happen immediately after the change type. But then, you have to do this individually in all the columns where the format error happens. In your case, the fields population-male , population-female was changed to decimal later in the step. The replace for these should happen immediately after this. I have created an additional query to show this. You can look at the steps. Also, in the desktop, you can view both the tables and see that the table using the 1st method has 3 rows less than the 2nd method table as the 3 error rows were removed in the 1st method.

    Hope this helps. If not let me know.



    ------------------------------
    Gopa Kumar
    Limner Consulting
    ------------------------------

    Attachment(s)

    pbix
    GD Population exo 01_GK.pbix   17.53MB 1 version


  • 24.  RE: [Data Format Error] We couldn't convert to Number

    Gold Contributor
    Posted 8 days ago
    Good morning all,
    I want to thank everyone who helped me solve this problem of error. Now I can solve this kind of problem by myself. But I will still need you for other problems; I am still a beginner!
    Thanks again,
    Gerard


    ------------------------------
    GD61
    ------------------------------