Forum Discussion
Suppressing blank query error message
Happy new year!
I realize it's PowerBI board, however I'm running PowerQuery for Excel purposes, but perhaps it doesn't play a big role. My PQ query loads xlsm files from a folder - which sometimes can be empty. The query fetches some named-range data from xlsm files to display and looks like this is where I run into issues. If there's nothing present, I'd like to receive a nice and civilized blank response, however once the query reaches M-code lines that fetch named range data, I'll get an error message that will result in a popup message (again - in a scenario when folder is empty):
[Expression.Error]: There weren't enough elements in the enumeration to complete the operation.
This also interrupts a scheduled process with this popup. On full disclosure: I launch this query from Excel via VBA-script and I could suppress this message by adding a "On Error Resume Next" command, though I wouldn't want to turn off error messaging fully.
The question to this board - is there a way to configure the query itself to just display blank result and not add the popup when the result is just a blank page? Looks like fetching specific data from inside the workbook is where things get derailed, before that line, I'll just get blank result when stepping through the M-code lines:
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File (9)", Table.ColumnNames(#"Transform File (9)"(#"Sample File (9)"))),
(there are several named-range data extractions, this is just the first one)
Thanks a lot!
Hello crossover
you can use the error handler "try" and "otherwise". so you could a statement like this
YourFinalResult = try Table.ExpandColumn(.....) otherwise YourExpectedResultWhenHittingAnError
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
1 Reply
Hello crossover
you can use the error handler "try" and "otherwise". so you could a statement like this
YourFinalResult = try Table.ExpandColumn(.....) otherwise YourExpectedResultWhenHittingAnError
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