Power Query Auditing / Errors

[Nick Burns]

Any one deal with a Power Query that returns rows, but with errors?
The connection states, 12 rows returned, 5 errors. I click on the "errors" hyperlink and of course it doesn't return any rows. You can see in the image below that the query successfully shows all data, but the Load doesn't export all the data hence the 5 errors.
Not sure how I have to audit this to trace down the errors.

 

[Ken Puls]

Hey Nick,

So two things...

What happens if you click the Refresh Preview in the editor? Does it reload the data and show your errors at that point?

When you do load and get errors, can you click the blue error count that shows in the Queries Pane? That will create a new query that adds rows numbers, then filters to show which rows had the errors.

Let me know how you make out.

 

[Nick Burns]

That's just it... refresh doesn't show any errors, just keeps showing the same table with the missing data.
When I click the errors link in the Queries pane, it creates the new query with an empty table since the data has no errors. I mention this in my post above.

This stems back to my other post regarding performance. The Excel file I'm reading the data from is 5MB. By the time it's reading this query, it's reading around 300MB.
What are some of the processes that can start multi-reading the data and what can I do to mitigate this? Will some Table.Buffer() steps help? If so, where in the process should they be used?

 

[Ken Puls]

I'll take a look for your other post as well, but I don't think a Table.Buffer() is going to help you here. One thing I do notice is that all the values that aren't showing up have decimals, and the column is formatted as a whole number. Normally, I would expect this to round, but I wonder if there is something there. Can you format the Hours column as a decimal to see if that has any effect? In addition, you should really set a data type for the first column too, as it is currently an "any" (variant.)

I'm curious if this fixes the issue or not.

 

[Nick Burns]

You were right about the data type. There was a Conditional Column that set the data type to a whole number - I hadn't noticed it :

= Table.AddColumn(#"Expanded ReportTypeRates", "Total", each if [Hours] = "A" then [Apprentice Hours] else if [Hours] = "!A" then [NonApprentice Hours] else List.Sum({[Apprentice Hours], [NonApprentice Hours]}), Int64.Type)

Once I reset the data type, the load worked:

= Table.AddColumn(#"Expanded ReportTypeRates", "Total", each if [Hours] = "A" then [Apprentice Hours] else if [Hours] = "!A" then [NonApprentice Hours] else List.Sum({[Apprentice Hours], [NonApprentice Hours]}), type number)