• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Using 'From File from Folder' doesn't handle errors or ''key' doesnt exist|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Using 'From File from Folder' doesn't handle errors or ''key' doesnt exist|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryUsing 'From File from Folder' doesn…
sp_PrintTopic sp_TopicIcon
Using 'From File from Folder' doesn't handle errors or ''key' doesnt exist
Avatar
Peter Morris
Member
Members
Level 0
Forum Posts: 16
Member Since:
July 20, 2019
sp_UserOfflineSmall Offline
1
May 3, 2021 - 2:06 am
sp_Permalink sp_Print

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.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
May 5, 2021 - 10:23 pm
sp_Permalink sp_Print

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

Avatar
Peter Morris
Member
Members
Level 0
Forum Posts: 16
Member Since:
July 20, 2019
sp_UserOfflineSmall Offline
3
May 8, 2021 - 7:41 am
sp_Permalink sp_Print

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

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1510
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
May 25, 2021 - 2:14 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
May 26, 2021 - 3:03 pm
sp_Permalink sp_Print

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.

Avatar
Peter Morris
Member
Members
Level 0
Forum Posts: 16
Member Since:
July 20, 2019
sp_UserOfflineSmall Offline
6
May 26, 2021 - 9:15 pm
sp_Permalink sp_Print

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.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
7
May 26, 2021 - 10:45 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Laurence Chapman, Ben Hughes, Alison West, Manjula Mangalmurti
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6201
Posts: 27185

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.