My Online Training Hub

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

IFERROR in Power Query Using TRY OTHERWISE

You are here: Home / Power Query / IFERROR in Power Query Using TRY OTHERWISE
try otherwise power query iferror
by

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.

Watch the Video

Subscribe YouTube

 

Download Sample Excel Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

 

First up, let's load data from this table.

sample data

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

errors in 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.

remove errors

Or I could use Replace Errors, but this doesn't give me any idea what the cause of the error is.

replace errors

I want to see what caused the error and to do this I'll add a Custom Column and use try [End]

try end

This creates a new column with a Record in each row

column of records

In this record are two fields. HasError states whether or not there's an error in the [End] column

error record

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

record with no error

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

expanding record column

Checking what's in the Error Records, you can see the Reason for the error, DataFormat.Error, this is from Power Query

examining error record

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.

expanded error column

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.

compact query

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

try if then else

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

try if then else result

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]

error dividing

this gives me an error as I know it will in rows 1 and 3

errors in calc column

so to avoid this, edit the step and use try .. otherwise

try otherwise to replace errors

now the errors are replaced with 0.

errors fixed

Errors Loading Data from A Data Source

I'll create a new query and load from an Excel workbook

new query from workbook

Navigating to the file I want I load it

loading workbook

and loading this table

loading table from workboiok

table loaded to power query

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.

change path to file

error loading file

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

try otherwise backup file

close the editor and now my backup file is loaded.

backup file loaded

try otherwise power query iferror

More Power BI Posts

combine files with different column names in power query
Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
easily compare multiple tables in power query using list functions
Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
fuzzy matching in power query
Use fuzzy matching to compare non-identical text strings and match them together based on how similar one string is to the other.
handling http errors in power query and power bi
Clearly communicate issues with custom messages when dealing with web scraping or API server errors. Download sample Excel and Power BI files
extract characters from strings in power query using text select and text extract
Learn a cool technique to extract or remove letters, numbers and special characters from strings. Sample workbook to download
highlighting data in power bi visuals
Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
shape maps in power bi
Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.
Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
sort by column in power bi
Create a Sort-By column to allow custom sort order in your Power BI Visuals. Download an example Power BI Desktop file
custom formatting strings in power bi
Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats

More Power Query Posts

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables
Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query
Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query
Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query
Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts
Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query
Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips
Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns
How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror
Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Tag:

Reader Interactions

Comments

  1. Julian

    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! 🙂

    Reply

    • Philip Treacy

      Hi Mallo,

      It works ok for #N/A in my testing. Can you please provide an example workbook showing where it doesn’t.

      Regards

      Phil

      Reply

  4. Murilo Valle

    Hello.

    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,

    Murilo Valle

    Reply

    • Philip Treacy

      Hi Murilo,

      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]),

      Regards

      Phil

      Reply

  5. Antonia

    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! 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *