Hi Fellow Power BI Users -
Just wondering if any of your videos cover error handling in M Code. I couldn’t see any on quick review.
I have two situations I’m trying to address:
-
List item When retrieving a folder of excel files, how to ignore any files that contain errors (and reporting which files were ignored). I’d prefer not to just delete the row or replace with blank as that may cause other issues which could go unnoticed.
-
List item When merging 2 x queries, preventing an error if there isn’t any data in one of the tables. By way of example, we have a detailed forecast template and a summary forecast template. As we get close to end of year everyone is using detailed templates so there are no rows in the query that consolidates the summary template data. This returns an error so I load a template with 1 cent to force it to succeed. It would be a better outcome if we could use something like IF Merge with table 2 fails, use table 1 else merge Tables 1+2
Thanks
Kirsty
There’s isn’t specific on error handling for a few reasons.
Firstly the error provided by the query editor are quite terrible currently, so there’s isn’t much too work off in most cases.
Secondly and most importantly there’s just too many variable in what could go wrong. For example you’re particular example are very unique.
Personally I also don’t like to teach people M code as pretty much everything you can do, can be done using the point and click interface. It’s just a matter of knowing what to do in which order.
It’s important to understand how M code works but actually writing it out, I’m not a big fan of. The auditing you can do within this area is so limited it’s too risky to go down that path in my view.
Below are more of my thoughts on M code
You’ll see the next video is around how to fix things if they fall over also.
In terms of actually assisting with the items you’ve mentioned, will need to see more around the example and basically what you’re seeing. It’s very difficult to understand every variable at play in your scenario and to work on a meaningful solution currently.
Chrs
Sam
Thanks Sam, I took a quick look at the videos and I think these are fantastic for learning but I’ve been using M Code and DAX heavily for over 12 months so the examples aren’t quite at the complexity I’m looking at.
I’ve found errors to be a major issue for trust from end users when Power BI can fall over based on one cell having a #N/A. Mostly I’ve been able to prevent these issues by putting strict controls in the spreadsheets to restrict data types that can be entered, prevent column headers being changed etc. But people never cease to amaze me at how they can be so efficient at breaking my controls (paste values being my worst enemy).
I suppose I am trying to push the boundaries to find innovative ways to use coding to discount a file if it causes a refresh to fail. If you are happy to look at this, I’ll put some time into a simple diagrammatic summary to show what I’m trying to achieve.
Thanks in advance,
Kirsty
PS. Agreed - I’m not a fan of the errors that return and how vague they can be either.
I think you’re on the right track around fixing or placing controls over the spreadsheets.
This in my view is the best option, rather than trying to account for every issue that could occur via code.
You have to remember the more you custom code in M the less chance anyone is ever going to understand what you’ve done if there’s another error. That’s just where the error handling is currently at within the query editor.
I’ve heard rumours of improvement in these area for some time…for almost 18 months actually, but haven’t seen any releases which is unfortunate.
You know that you can fix those N/A errors quite easily though just using the interface. Right click on a column and go remove errors.
Thanks
Sam
Thanks Sam, for such a fantastic product their error handling is disappointing. Still benefits of using Power BI far outweigh the costs.
Thanks again for your insight.
KC