Log In
Register
Home
July 20, 2019
Offline
I'm trying to handle potential errors in source data. Currently the 49 files of interest generate approximately lines of data - nothing very significant.
However the source files can have errors in them, such a '#Ref' that occur when a formula is unable to update due to deletion. In the sample import when using 'New Query, From File, From Folder' I've selected all columns and converted errors to something usable. However, when then importing the complete data set, I can see a number of errors accruing in the download and finally a message that reports to the effect that a 'Key' is missing. I haven't set a key nor is the output table linked to anything. Neither am I merging the data with any other data set.
I'm completely stumped at the moment - I don't even know where to start and there appears to be little information on how Powerquery actually handles errors when importing data. I don't want to ignore files with errors as that will significantly undermine the utility of project.
Grateful for any thoughts and/or advice.
October 5, 2010
Offline
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
July 20, 2019
Offline
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
October 5, 2010
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Peter,
Using the automatic file combiner from folder is not flexible, try following the instructions and sample files from this topic:
https://www.myonlinetraininghu.....uery-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.
July 20, 2019
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
1 Guest(s)
