• 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
    • 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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
August 19, 2021 by Philip Treacy

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.
Please enter a valid email address.

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.

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

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

Easily Compare Multiple Tables in Power Query

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

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

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

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

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

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

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.

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

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

Sort-By Columns 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

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

power query variables

Power Query Variables 3 Ways

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

Remove Blank Rows and Columns from Tables in 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

Extracting Data from Nested 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

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

Power Query Keyboard Shortcuts to Save Time

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

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

Pivot an Unknown Number of Rows into 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

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
Category: Power QueryTag: Power BI
Previous Post:easily compare multiple tables in power query using list functionsEasily Compare Multiple Tables in Power Query
Next Post:Dynamic Dependent Data Validationdynamic dependent data validation

Reader Interactions

Comments

  1. Julian

    November 26, 2022 at 10:52 pm

    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
    • Mynda Treacy

      November 27, 2022 at 7:47 am

      Thanks for sharing, Julian!

      Reply
  2. José Andrés

    June 8, 2022 at 8:32 am

    Thanks!!!

    Reply
    • Philip Treacy

      June 8, 2022 at 2:57 pm

      You’re welcome.

      Phil

      Reply
  3. Mallo

    February 24, 2022 at 3:28 am

    Hi Mynda, thank you very much for your post.. it doesn’t work for #N/A values.. do you know why?
    Thanks in advance!

    Reply
    • Philip Treacy

      February 24, 2022 at 9:53 am

      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

    August 20, 2021 at 11:07 pm

    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

      August 23, 2021 at 10:55 am

      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

    August 20, 2021 at 9:34 pm

    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
    • Mynda Treacy

      August 21, 2021 at 9:50 am

      So pleased to hear that, Antonia!

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • 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
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

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.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x