Partners filmexxx.link, sextotal.net, ahmedxnxx.com, https://xnxx1xvideo.com, russianxnxx

In this post, I am going to talk about the IFERROR function in Power Query. There is no explicit IFERROR function in Power Query but we have try and otherwise statement which works similar to the IFERROR function in Excel.

 

IFERROR in Power Query (try and otherwise) – Video

 

Power Query try and otherwise – Example 1

  1. Consider this sample data. I have Employee ID, Total Pay, and Hours.
  2. I created a Custom Column to calculate Pay per Hour
= [Total Pay] / [Hours]

Simple Data Model with error

Note – Emp ID 6 returns an error because 200 is divided by a text = “nil”. To correct the above error, I am going use try and otherwise within the formula

= try [Total Pay]/[Hours] otherwise null

As an output, I got a null instead of an Error.

Datamodel with null

 

Power Query try and otherwise – Example 2

The try and otherwise statement can also be applied to the entire step.

  1. I deleted the Custom Column for Pay/Hours
  2. And Changed Type, results in an error since it wasn’t able to find the previous step

Error

This is a step-level error that happened at the Changed Type Step.

To fix this error I will surround my entire formula with the try and otherwise statement in the formula bar.

= try
    Table.TransformColumnTypes(Source, {{'Pay/Hours', type number}})
otherwise
    #'Removed Columns'

If the above formula (which is trying to change the type of Pay/Hours) returns an error then the otherwise statement returns the previous step – Removed Columns. The query throws no errors.

Datamodel Example 2

 

Power Query try and otherwise – Example 3

This time let’s write the try statement (without otherwise). Let’s calculate Pay/Hours but this time with only the try keyword.

Create a new Custom column with the following formula,

= try [Total Pay]/[Hours]

As a result, it gives me a column that contains records.

Datamodel Record Example 3

Expanding Custom column is going to return 3 more columns.

Datamodel Expanded Custom

  1. HasError – Shows TRUE if the formula resulted in an Error.
  2. Value – Is the output of the formula with no errors.
  3. Error – Again contains records that will describe the error upon expanding.

Upon further expanding the Error column.

Datamodel expanded Error

We again get 3 more columns,

  1. Reason – This tells the reason for the error.
  2. Message – This shows what actually the error is.
  3. Detail – Tells the information of the error, message about the error.

On further expanding Detail column we get even more details about the error on that particular row.

Datamodel Expanded Detail

So this is a very crude way of doing some very basic error reporting on your data in case your Power Query steps result in any errors.

 

    ⬇️ Pop in your Name & Email to get the file!


     

    More on Power Query

    Promote Double Headers in Power Query

    5 Tricks to Reduce Steps in Power Query

    Remove Top Rows and Combine Data from Multiple Excel Files

     

    Quick VLOOKUP in Power Query

    Dynamic Unpivoting in Power Query

     

     



    Topics that I write about...