Hi Peter,

When you get an error that states aa key is missing it usually refers to a missing column, or a column that has been renamed, or a missing/renamed sheet.

Without some sample data to work with it's difficult to be certain. Can you supply your query and some files that generate these errors?

regards

Phil

Hi Phil,

I'm rather constrained on what I can share due to company rules.  The key thing I'm trying to understand is how to 'trap' an error that occurs and causes the query to fail.  I think I've trapped errors that end up in columns or cells and have ensured that all sheets being imported are correctly structured.  The real problem is that the code fails but I've no way of identifying exactly where in the code it failed or which file caused the problem.  My current technique is to filter the import to half the files and see what happens - if successful, split the other half (which therefore contains the error) and repeat until the file is identified.

I read Ben Gribaudo's blog on error handling and he identifies that an error that isn't 'contained' causes the query to halt.  But he doesn't explain a strategy to trap those high level errors.  Any advice on this and debugging powerquery generally would be gratefully appreciated.

I'm back at work on Monday and may be able to drill further into the problem and will share what I can.

Apologies for the delay in replying.

Regards

Peter

Hi Peter,

To trap errors in PQ use try .. otherwise.  Here's an example where I call the List.Sum() function but don't pass in a list

let
Source = try List.Sum() otherwise 0
in
Source

Ordinarily calling a function that requires arguments, and not supplying them, would generate an error.  Wrapping the function call in try .. otherwise catches the error and outputs whatever is specified by otherwise if an error occurs, which in this case would be 0.

Regards

Phil

Hi Peter,
Using the automatic file combiner from folder is not flexible, try following the instructions and sample files from this topic:
https://www.myonlinetraininghub.com/excel-forum/power-query-members/source-data-column-headings-have-changed-throwing-a-power-query-error

Instead of having hard typed column names in your query, which will most likely fail on some files that are missing a column, use Table.ColumnNames to select all columns in a table to replace errors.

Hi Phil,  I do use 'try .... otherwise' in certain areas, but as I understand it I can only use that construct over individual lines of code.  That would imply that I would need to wrap each line separately; is that what you mean?   I was thinking that there should be a construct similar to 'on error goto ??' in VBA which then traps errors that occur in any row of the code.

Hi Catalin, I'm not sure that that is the cause because when that has happened (if I recall correctly) I've received a manageable error.  However, where I've noticed possible problems of that type I am using Table.ColumnNames to avoid hard coded names.  I've also found it to be a useful tool in allowing me to filter columns and move columns.

Many thanks for the help - its appreciated.

As Phil mentioned, hard to debug without seeing.

We need to see sample data, not your real data, there cannot be any company rule to to stop you from posting anonymized data that replicates your errors.