Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Gérard Ducouret]

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
——————————

[John Henning]

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
——————————
——————————————-

[Vishesh Jain]

Hi ?

I agree with , 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
——————————
——————————————-

[Gérard Ducouret]

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
——————————
——————————————-

[Christopher Schnaars]

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
——————————
——————————————-

[Gérard Ducouret]

Thanks Christopher,

How can I open a pbix file with Excel ?

Thanks again

Gérard

——————————
GD61
——————————
——————————————-

[Michael Taubman]

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
——————————
——————————————-

[Vishesh Jain]

Hi ??,

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
——————————
——————————————-

[Gérard Ducouret]

Hi Vishesh,

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

Thanks

——————————
GD61
——————————
——————————————-

[Gérard Ducouret]

Michael,

The only thing I get with “Keep Errors” is a yellow message: “DataFormat Error”

What did I miss?
Thanks

——————————
GD61
——————————
——————————————-

[Michael Taubman]

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

——————————
——————————————-

[Gérard Ducouret]

Yes, I tried to remove #N/A, but with no avail. :-(?

——————————
GD61
——————————
——————————————-

[Christopher Schnaars]

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
——————————
——————————————-

[Gérard Ducouret]

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
——————————
——————————————-

[Gérard Ducouret]

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
——————————
——————————————-

[Vishesh Jain]

Hi ??,

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

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
——————————
——————————————-

[Gopa Kumar Sivadasan]

Hi

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
——————————
——————————————-

[Gérard Ducouret]

Hi @Gopa Kumar?,

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

Thanks

——————————
GD61
——————————
——————————————-

[Audrey Abbey]

What did you replace the errors with?

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

[Gérard Ducouret]

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
——————————
——————————————-

[Michael Taubman]

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
——————————
——————————————-

[Gérard Ducouret]

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
——————————
——————————————-

[Gopa Kumar Sivadasan]

Hi

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 1^st method has 3 rows less than the 2^nd method table as the 3 error rows were removed in the 1^st method.

Hope this helps. If not let me know.

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

[Gérard Ducouret]

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
——————————
——————————————-

[Erick Nyatenya]

Hi Gerard,
I hope I am not too late to the party. I found a solution that worked for me when I had this same problem. In your steps, you will see the “Changed Type” in power query editor. If you click on the formula bar, you should be able to edit the type to text from Int64.

——————————
Erick Nyatenya
Founder
Coral Springs
——————————
——————————————-