Data Format Error

[FW94]

Hello,

My dataset in the query editor shows no errors however when I close and apply my changes there is one error. There is no option to view the error from the first dialogue box and so when I go back into the query editor and select "keep errors" I am greeted with the following:

DataFormat.Error: We couldn't convert to Number.
Details:
R-P51

I have one column which is in number format and have checked both Power BI and the original dataset for "R-P51" however there is no cell containing this. Power BI is also not directing me to the actual cell which is causing the error.

I have checked other questions people have posted however nothing has helped me with this. Would anyone have any ideas please?

Thanks

[v-yetao1-msft]

Hi @FW94 

I am glad to hear that you have solved this problem. Could you mark your own answer so that you can help other people with the same problem find a better solution .

Best Regards

Community Support Team _ Ailsa Tao

[parry2k]

@FW94 great to hear. that's why I asked for the M code, I wanted to check if there is something happening in the earlier steps. Well, good to hear it is all solved now. 

[parry2k]

@FW94 click advanced editor and paste the full code.

[FW94 · Accepted solution]

@parry2k I've managed to solve now. It looks as though an error was created during earlier stages where I split a column, changed their formats and carried out some data cleansing, and re-merged them, changing their format a final time. Once these steps had been removed, the changes were applied without problem. 

Thank you for your time and effort in helping

[parry2k]

@FW94 interesting, can you change the type of this column to text for the testing purpose and see if it loads correctly. Can you share M code here?

[FW94]

@parry2kI have ran the test as suggested and when I change this to text and "apply" I am greeted with the same error message as in my original post.

The M Code when selecting "keep errors" is as follows:

= Table.SelectRowsWithErrors(#"Filtered Rows3")

Apologies if this wasn't the M Code you were after if. If not, let me know which you are referring to and I will post.

[parry2k]

@FW94 so basically there is a column that is converted to a number with the text value. if you have multiple columns that are getting converted to number, change their type to text and change type of one column to number at a time to see which column is giving the error.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[FW94]

@parry2k I only have one column which is set as a number format and having checked this both in Power BI using the search filter, as well as the original data set in Excel, I can't find the error

[HotChilli]

Have you checked the option 'column profiling based on entire data set' (rather than first 1000 rows)?

[FW94]

@HotChilli just tried this (that's also a new feature to me so thank you as a side note for the introduction) and I receive the following error message:

"We encountered an error while computing the distribution"

For clarity, it works fine on the first 1,000 rows. The above message only appears when I change this to the whole dataset