In Excel we can use IFERROR to if our calculation results in an error, and we can then tell Excel to produce a different result, instead of the error.
Power Query doesn't have IFERROR but it does have a way of checking for errors and replacing that error with a default answer, it's called try otherwise
In this post I'll show you how to use try otherwise to handle errors when loading data, how to handle errors in your transformations and how to handle errors when your query can't locate a data source.
Download Sample Excel Workbook
Enter your email address below to download the sample workbook.
Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.
First up, let's load data from this table.
I've already generated a couple of errors in this table, and of course I can obviously see them and I could fix them before loading into Power Query.
But when using Power Query this isn't always the situation. Your query will be loading data without knowing what it is so how would it handle these errors?
Let's load the data into Power Query and call it Errors from Sheet
Straight away you can see the errors in the column.
Now of course you could use Remove Errors but that would remove the rows with the errors and that's not what I want.
Or I could use Replace Errors, but this doesn't give me any idea what the cause of the error is.
I want to see what caused the error and to do this I'll add a Custom Column and use try [End]
This creates a new column with a Record in each row
In this record are two fields. HasError states whether or not there's an error in the [End] column
If there is an Error then the 2nd field is another record containing information about that error
If there isn't an error, then the 2nd field is the value from the [End] column
If I expand the new column I get 3 new columns containing the HasError value which is boolean, and either an Error or a Value
Checking what's in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query
There's the Message, which is the error from the Excel sheet, and some errors give extra Detail, but not in this case.
If I expand this Error column I can see all of these fields.
I've ended up with a lot of extra columns here and it's a bit messy so let's tidy it up. In fact I'll duplicate the query and show you another way to get the same information in a neater way
The new query is called Errors from Sheet (Compact) and I've deleted all steps except the first two.
What I want to do is , check for an error in the Try_End column, and if there is one I want to see the error message from Excel.
If there isn't an error I want the value from the [End] column.
I can do all of this in a new column using an if then else
Add a new Custom Column called Error or Value and enter this code
What this is saying is:
- If the boolean value [HasError] in the [Try_End] column is true then
- return the [Message] in the [Error] record of the [Try_End] column
- else return the [Value] from the [Try_End] column
With that written I can remove both the End and Try_End columns so the final table looks like this
Checking for Errors and Replacing Them With Default Values
In this scenario I don't care what the error is or what caused it, I just want to make sure my calculations don't fail.
I duplicate the original query again, calling this one Error in Calculation, and remove every step except the Source step
I add a new Custom column called Result and what I'll do here is divide [Start] by [End]
this gives me an error as I know it will in rows 1 and 3
so to avoid this, edit the step and use try .. otherwise
now the errors are replaced with 0.
Errors Loading Data from A Data Source
I'll create a new query and load from an Excel workbook
Navigating to the file I want I load it
and loading this table
I'm not going to do any transformations because I just want to show you how to deal with errors finding this source file.
I'll open the Advanced Editor (Home -> Advanced Editor) and change the path, so that I know I'll get an error. Here I change the drive letter to X.
I don't have an X: drive so I know this will cause the workbook loading to fail.
So that's what happens when the file can't be found so let's say I have a backup or alternate file that I want to load if my main file can't be found.
Open the Advanced Editor again and then use try otherwise to specify the backup file's location
close the editor and now my backup file is loaded.
I found that “try/otherwise” looking for alternative files doesn’t work. I still get the “file not found” error. However, if I enclose the first source file reference in “Table.Buffer ()” then it works.
So in your example, I would write
Source = try Table.Buffer ( Excel.Workbook(File.Contents(“X:\Data\try_otherwise_data.xlsx”), null, true) ) otherwise ( Excel.Workbook(File.Contents(“D:\Data\try_otherwise_data.xlsx”), null, true),
… and it works!
I read somewhere that Power Query is “lazy”, so you have to “force” it to “try” by using “Table.Buffer ()”. I don’t know why, but anyway, the Table.Buffer trick works for me.
Hope that helps someone! 🙂
Thanks for sharing, Julian!
Hi Mynda, thank you very much for your post.. it doesn’t work for #N/A values.. do you know why?
Thanks in advance!
It works ok for #N/A in my testing. Can you please provide an example workbook showing where it doesn’t.
Great video. I’d like to know if the ‘Load from Workbook’ example also works with different options as from CSV or folder. I’ve tried applying the same to queries I already had built but the loading error persists.
Thanks in advance,
Yes it does work with Load from Folder and from CSV, but it sounds like you’ve run into a gotcha problem with PQ because of the way it ‘lazily’ evaluates some things.
If you try this
Source = try Folder.Files("d:\temp") otherwise Folder.Files("c:\temp")
and d:\temp does not exist but c:\temp does exist, you’ll still get an error because PQ doesn’t properly evaluate d:\temp.
To make this work you have to force PQ to check that d:\temp exists and you do that using the Table.Buffer() function.
If you do this, then PQ will correctly assess that d:\temp does not exist and will then try to load c:\temp
Source = try Table.Buffer(Folder.Files("d:\temp")) otherwise Folder.Files("c:\temp")
It’s the same for loading from CSV, wrap the first attempt to load a CSV in Table.Buffer()
Source = try Table.Buffer(Csv.Document(File.Contents("d:\temp\temp-data.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None])) otherwise Csv.Document(File.Contents("c:\temp\temp-data.csv"),[Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
The last example has literally saved me and my team a lot of time and resources, it has been a frustration for months! I didn’t even realise it could have been resolved with a simple solution like this one, but as soon as I read your newsletter, I applied this, tested it and it works like a charm. Beautiful!
I am so grateful for all your hard work on this blog and videos and just really wanted to let you know that I am a massive fan of your work. Please keep it going! 🙂
So pleased to hear that, Antonia!