IFERROR in Power QUERY

Keep trying

Power Query does not have an IFERROR function. It does however have another function that performs the same type of calculation.

Let’s take the example data below.

The name of the person is in the same column as the date. This layout is not conducive to building reports but we can use Power Query to easily convert it into a proper data set.

You can download the file using the button at bottom of the page.

There are a number of ways to tackle this example. I want to demonstrate the try function in Power Query.

In the final output we need another column to capture the name for each row.

This example uses Excel 2016. Power Query is available for older Excel versions but you need to install a free Microsoft add-in.

Click inside the table and click the Data ribbon tab and click the From Table/Range icon.

Click OK when the Create Table dialog opens up and that will take you to the Power Query window.

Click the Add Column ribbon tab.

Click the Custom Column icon.

In the formula box enter – note these formulas are all case-sensitive, so be careful.

=try Date.From([#”Name-Date”]) otherwise null

And click OK. It should look like the image below.

The Date.From function expects a date. If it doesn’t receive a date it returns an error. The try function tries to do the date calculation. If it is a date it does the date calculation. When it hits the text of the name it causes an error and it uses the alternate entry, in this case null.

We can add another Custom column. This will use a short if function to capture the name when null appears in the first Custom column.

=if [Custom] = null then [Name-Date] else null

We can then use Fill Down on the second Custom column to populate the names.

Then we can use the first Custom column to filter out the name rows by excluding null from the filter.

 

We don’t need the first Custom column any more so we can delete it.

We can now rename the columns to Date and Name and change the data type of the Date column to Date. See image below.

 

We can now use Close & Load. The final data set is shown below.

(I had to try very hard to stop myself from doing any Yoda jokes with this post.)

Download example file

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.