Search code examples
sqlerror-handlingpowerbipowerquerydatasource

Power Query Data Source error-handling - skip further steps if an error is encountered

Good day

I have a Power Query query that pulls its Source from a Sql.Database() query. Sometimes the core tables are dropped pre-pull which creates a Data Source error as follows:

DataSource.Error: Microsoft SQL: Invalid object name 'sqldb-prod.123_outcomes.1234_outcome'.. DataSourceKind = SQL. DataSourcePath = tcp:sqlsvr-prod.public.12345.database.windows.net, 3342;sqldb-reporting-prod. Message = Invalid object name 'sqldb-prod.123_outcomes.1234_outcome'.. ErrorCode = -2146232060. Number = 50000. Class = 16. . The exception was raised by the IDbCommand interface. Table: 1234_example.

My question on this is whether there is some IF or Try otherwise M-code I can add so that it just generates a null outcome or empty table and that it automatically skips any transformation steps thereafter?

The last thing I want is an email to the customer daily with a failure message (error has occurred). I understand the error and would like to handle it in code.

Thanks in advance for the assistance.

Solution

  • You just wrap your statement in try otherwise. The code below creates an empty table.

    try...
otherwise 
#table({},{})

    There is also try...catch now too.

    https://learn.microsoft.com/en-us/power-query/error-handling

    I'm not sure how this will help though as no base table will be loaded and everything downstream will still presumably break.