Error loading table but query editor shows no error rows

[ROKRI]

Hello,

When I refresh my table in PBI desktop the data loads but I get a message there are 10 errors (image 1). However, when I view in query editor, the table is empty with no error rows, only the steps listed in applied steps on the right of image 2.

I cannot figure out what is happening. I have already resovled any cell specific errors. Seems the data loads fine but I'm concerned about not being able to resolve the errors.

Interestingly, this data set is a compilation of three separate data sets, each with their own PBI setup, no errors. I compile the excel data manually from the three separate sets.

Please help. Thank you.

Russ

[chamullerousa]

I had this same issue and then noticed I had declared the data type in my new column creation step. When I removed the ", type number" and then added a separate Changed Data Type step, I got no errors.

[ROKRI]

@Jimmy801 @dax 

I solved the 2 issues related to blank error tables because some calculations were returning NaN, though, none of these cells were showing as error. The error table was blank. I wrote the following formula to avoid the NaN "= Table.AddColumn(#"Added Receivable Balance", "Current FY Actual ICR %", each if [FY20 Actual Direct]=0 then "null" else ([2020 Expenditure]-[FY20 Actual Direct])/[FY20 Actual Direct])".

Two questions:

1) Why does PBI not show NaN as an error in the error table?

2) How would I write it as an iferror(calculation,0) formula? I've read a number of posts and it seems to be complicated m code though I imagine there's got to be an easier way.

Thank you,

Russ

[Jimmy801 · Accepted solution]

Hello @ROKRI 

first of all I would reccomend to use this formula, instead of yours

= Table.AddColumn(#"Added Receivable Balance", "Current FY Actual ICR %", each if [FY20 Actual Direct]=0 then null else ([2020 Expenditure]-[FY20 Actual Direct])/[FY20 Actual Direct], type number)

otherwise you have a mixture of data-type text and number. 

In Power Query you can use the try otherwise syntax to get the errors, but I think the NaN is not covered here. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[ROKRI]

Great @Jimmy801 . Thank you for the recommendation and support!

Russ

[Jimmy801]

Hello @ROKRI 

are you using a Table.SelectRows in your query?

Jimmy

[ROKRI]

Hi @Jimmy801,

Thanks for the response.

I am not using Table.SelectRows in this instance as I'm working off the master table and need all data included.

I wasn't familiar with this (still learning) and I think this is just what I need to work always out of one table as opposed to the multiple ones. Great idea, though, not my current issue.

With reference to @dax comment about merging the tables, I meant I have three separate excel files I manually combine into a master one. To be specific, three regional files, Asia, Africa, Middle East I combine to a Global file, manually in excel. The three regional files load fine into PBI desktop, no errors in either deskop or query editor. But the issue is still as initially described, empty error table in editor.

For further insight, the same thing happened today when updating the Middle East file with new columns and data. New columns added include FY 2020 data, multiple columns. I'm having the same problem, "1 of the loaded queries contained errors", but the query editor error table is blank. As per @dax prior comment, I did resolve a bunch of initial errors preventing data load such as text being in number columns and similar issues prevening data load. With all that resolved, I can now load data but still get error message as per below, however, data is there with empty error table in query editor (second image). Same as issue with global one as per initial post.

I appreciate the continued support.

Russ

[dax]

Hi @ROKRI , 

You could click error to see detailed error information. In addition, you also need to check each step to see which step have error and try to modify it to see whether it work or not.

 And you said that when they are seperate, they are ok, so did you mean that when they combine, something wrong in steps? Please check it.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ROKRI]

Hello @dax ,

Thank you for the response.

I did check the detailed error information by clicking the link. I've also gone through each step and fixed errors as they occur. The final step in the applied steps is clean, all data loads fine without errors, but when I go into PBI desktop and refresh, I'm still getting the error notice. Though, the table in query is blank. I am familiar with instances where there are actual errors loading data in PBI desktop and then am able to go into query editor and resolve. However, this time, there's no query errors to resolve. Just the blank table as per image.

Perhaps it has something to do with column order or new columns added or something. Again, all the data is there.

Does that clarify?

Thanks,

Russ